Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

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

Le 12/08/2015 12:10, Jean-Michel Pouré - GOOZE a écrit :
> Another issue about:
> CREATE INDEX connect_idx
>   ON "connect"
>   USING btree
>   (src , sender_domain , sender_name);
>
> This is an index on three columns. It is effective only if the SQL
> query includes these three columns.

No, that's not the case. It is used when only the first or the first and
second column are used in a query.

>  It can be very fast, but it should
> not be considered the same as THREE different indexes. I agree it is
> not very well documented in PostgreSQL documentation, but all databases
> work the same and this is only effective in a limited number of
> queries.

And the order of the columns in this index was used so that all SQLgrey
queries can be sped up by this index. Having three indexes instead of
one would be both slower when updating or inserting data in the database
and when selecting records based on conditions on multiple columns like
SQLgrey does.
You only have to use EXPLAIN on queries done by SQLgrey on your database
to verify this.

Best regards,

Lionel.