Re: Question on a select - Mailing list pgsql-general

From Vincent Hikida
Subject Re: Question on a select
Date
Msg-id 00c601c4f0b1$c3944bd0$6501a8c0@HOMEOFFICE
Whole thread Raw
In response to Question on a select  (Madison Kelly <linux@alteeve.com>)
Responses Re: Question on a select
List pgsql-general
>
>   The indexes are:
>
> CREATE INDEX file_info_#_display_idx ON file_info_# (file_type,
> file_parent_dir, file_name);
> CREATE INDEX file_set_#_sync_idx ON file_set_# (fs_name, fs_parent_dir,
> fs_type)
>
>   Are these not effective for the second query? If not, what should I
> change or add? If so, would you have any insight into why there is such an
> incredible difference in performance?
>

I didn't look at your indexes closely enough. When you have concatenated
index, you want to have the most selective colum first. I guess that
file_type is not very selective. file_name is probably the most selective.
In the above, the index on file_set_# is optimal. The index on file_info_#
is suboptimal.

However, if the query is doing a hash join or sort merge, an index is not
used so the index doesn't matter. However, you probably do other queries
that do use the index so it should be fixed.

Vincent


pgsql-general by date:

Previous
From: "Vincent Hikida"
Date:
Subject: Re: Question on a select
Next
From: "Joost Kraaijeveld"
Date:
Subject: