Re: Index ot being used - Mailing list pgsql-performance

Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>
>>   Here is my full query:
>
>
>>tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type
>>FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC,
>>file_name ASC;
>
>
>>   This is my index (which I guess is wrong):
>
>
>>tle-bu=> \d file_info_7_display_idx
>>  Index "public.file_info_7_display_idx"
>>      Column      |         Type
>>-----------------+----------------------
>>  file_type       | character varying(2)
>>  file_parent_dir | text
>>  file_name       | text
>>btree, for table "public.file_info_7"
>
>
> The index is fine, but you need to phrase the query as
>
>     ... ORDER BY file_type, file_parent_dir, file_name;
>
> (Whether you use ASC or not doesn't matter.)  Otherwise the planner
> won't make the connection to the sort ordering of the index.
>
>             regards, tom lane

Hi Tom and Bruno,

   After sending that email I kept plucking away and in the course of
doing so decided that I didn't need to return the 'file_type' column.
Other than that, it would see my query now matches what you two have
recommended in the 'ORDER BY...' front but I still can't get an index
search.

   Here is the latest query and the new index:

tle-bu=> \d file_info_7_display_idx;
Index "public.file_info_7_display_idx"
      Column      | Type
-----------------+------
  file_parent_dir | text
  file_name       | text
btree, for table "public.file_info_7"

tle-bu=> EXPLAIN ANALYZE SELECT file_parent_dir, file_name, file_display
FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC,
file_name ASC;
                                                          QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=14509.53..14571.76 rows=24895 width=114) (actual
time=19995.250..20123.874 rows=25795 loops=1)
    Sort Key: file_parent_dir, file_name
    ->  Seq Scan on file_info_7  (cost=0.00..11762.44 rows=24895
width=114) (actual time=0.123..3228.446 rows=25795 loops=1)
          Filter: ((file_type)::text = 'd'::text)
  Total runtime: 20213.443 ms

   The 'Sort' is taking 20 seconds on my pentium III 1GHz (not great,
but...). If I follow you right, my index is 'file_parent_dir' first and
'file_name' second (does order matter?). So I figured the query:

SELECT file_parent_dir, file_name, file_display
FROM file_info_7
WHERE file_type='d'
ORDER BY file_parent_dir ASC, file_name ASC;

   Would hit the index for the sort. Is there any other way other than
'EXPLAIN ANALYZE...' to get a better understanding of what is happening
in there? For what it's worth, there is a little under 300,000 entries
in this table of which, as you can see above, 25,795 are being returned.

   Yet again, thank you both!! I'm off to keep trying to figure this out...

Madison

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-