Skip site navigation (1) Skip section navigation (2)

Re: Index ot being used

From: Madison Kelly <linux(at)alteeve(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, bruno(at)wolff(dot)to
Subject: Re: Index ot being used
Date: 2005-06-12 22:52:05
Message-ID: 42ACBC95.9020404@alteeve.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Madison Kelly <linux(at)alteeve(dot)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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

In response to

Responses

pgsql-performance by date

Next:From: eleinDate: 2005-06-13 00:30:49
Subject: Resource Requirements
Previous:From: Jacques CaronDate: 2005-06-12 17:57:54
Subject: Re: Updates on large tables are extremely slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group