From: Madison Kelly <linux@alteeve.com>
To: pgsql-performance@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: bruno@wolff.to
Subject: Re: Index ot being used
Date: Sun, 12 Jun 2005 18:52:05 -0400
Message-ID: <42ACBC95.9020404@alteeve.com> (raw)
In-Reply-To: <24636.1118591783@sss.pgh.pa.us>
References: <1825.216.209.137.1.1118419819.squirrel@mail.alteeve.com>
<20050610155535.GO8451@tobias.nordicbet.com>
<42AC42CB.8050703@alteeve.com>
<24636.1118591783@sss.pgh.pa.us>
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: linux@alteeve.com, tgl@sss.pgh.pa.us, bruno@wolff.to
Subject: Re: Index ot being used
In-Reply-To: <42ACBC95.9020404@alteeve.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox