From: Madison Kelly <linux@alteeve.com>
To: pgsql-performance@postgresql.org
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Index ot being used
Date: Mon, 13 Jun 2005 13:50:51 -0400
Message-ID: <42ADC77B.7000007@alteeve.com> (raw)
In-Reply-To: <20050613132852.GA31142@wolff.to>
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>
<42ACBC95.9020404@alteeve.com>
<20050613030001.GB17491@wolff.to>
<20050613031317.GA18988@wolff.to>
<42AD008D.4080600@alteeve.com>
<20050613035346.GA20703@wolff.to>
<42AD0B94.9090508@alteeve.com>
<20050613132852.GA31142@wolff.to>
Bruno Wolff III wrote:
> I am guessing you mean 'file_type' instead of 'file_info'.
>
> To do this efficiently you want an index on (file_type, file_parent_dir,
> file_name). Currently you only have an index on (file_parent_dir, file_name)
> which won't help for this query. You also need to order by file_type
> even though it will be constant for all of the returned rows in order
> to help out the planner. This will allow an index scan over the desired
> rows that returns them in the desired order.
>
> Please actually try this before changing anything else.
If I follow then I tried it but still got the sequential scan. Here's
the index and query (copied from the 'psql' shell):
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"
tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display
FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC,
file_parent_dir ASC, file_name ASC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Sort (cost=14810.92..14874.65 rows=25490 width=119) (actual
time=15523.767..15731.136 rows=25795 loops=1)
Sort Key: file_type, file_parent_dir, file_name
-> Seq Scan on file_info_7 (cost=0.00..11956.84 rows=25490
width=119) (actual time=0.132..2164.757 rows=25795 loops=1)
Filter: ((file_type)::text = 'd'::text)
Total runtime: 15884.188 ms
(5 rows)
If I follow all three 'ORDER BY...' items match the three columns in
the index.
Again, thanks!
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, bruno@wolff.to, tgl@sss.pgh.pa.us
Subject: Re: Index ot being used
In-Reply-To: <42ADC77B.7000007@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