Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

[Lionel B. <lio...@bo...>]

Le 12/08/2015 10:57, Jean-Michel Pouré - GOOZE a écrit :
> [...]
> * If we take the example of from_awl table, there are only two indexes:
> main OID

No:

sqlgrey=> \d from_awl
                 Table "public.from_awl"
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
 sender_name   | character varying(64)       | not null
 sender_domain | character varying(255)      | not null
 src           | character varying(39)       | not null
 first_seen    | timestamp without time zone | not null
 last_seen     | timestamp without time zone | not null
Indexes:
    "from_awl_pkey" PRIMARY KEY, btree (src, sender_domain, sender_name)
    "from_awl_lseen" btree (last_seen)


There is no index on OID in the SQLgrey database.

>  and lastseen. Were need more:
> CREATE INDEX from_awl_sendername
>   ON from_awl
>   USING btree
>   (sender_name);
>
> CREATE INDEX from_awl_senderdomain
>   ON from_awl
>   USING btree
>   (sender_domain);
>
> CREATE INDEX from_awl_src
>   ON from_awl
>   USING btree
>   (src);
>   
> CREATE INDEX from_awl_fseen
>   ON from_awl
>   USING btree
>   (first_seen);

You don't need these indexes and switching from the from_awl_pkey to
these indexes would slow SQLgrey. Run EXPLAIN on any query done by
SQLgrey and you should see that it uses the from_awl_pkey index (if it's
not, your database is probably not in a sane state).

Best regards,

Lionel