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 00:29:08 -0400
Message-ID: <42AD0B94.9090508@alteeve.com> (raw)
In-Reply-To: <20050613035346.GA20703@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>
Bruno Wolff III wrote:
> On Sun, Jun 12, 2005 at 23:42:05 -0400,
> Madison Kelly <linux@alteeve.com> wrote:
>
>>As you probably saw in my last reply, I went back to the old index and
>>tried the query you and Tom Lane recommended. Should this not have
>>caught the index?
>
>
> Probably, but there might be some other reason the planner thought it
> was better to not use it. Using indexes is not always faster.
>
> It would help to see your latest definition of the table and indexes,
> the exact query you used and explain analyze output.
>
Okay, here's what I have at the moment:
tle-bu=> \d file_info_7 Table
"public.file_info_7"
Column | Type | Modifiers
----------------------+----------------------+-----------------------------------------
file_group_name | text |
file_group_uid | bigint | not null
file_mod_time | bigint | not null
file_name | text | not null
file_parent_dir | text | not null
file_perm | text | not null
file_size | bigint | not null
file_type | character varying(2) | not null default
'f'::character varying
file_user_name | text |
file_user_uid | bigint | not null
file_backup | boolean | not null default true
file_display | boolean | not null default false
file_restore_display | boolean | not null default false
file_restore | boolean | not null default false
Indexes:
"file_info_7_display_idx" btree (file_parent_dir, file_name)
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_name, file_parent_dir, file_display
FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC,
file_name ASC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Sort (cost=15091.53..15165.29 rows=29502 width=114) (actual
time=12834.933..12955.136 rows=25795 loops=1)
Sort Key: file_parent_dir, file_name
-> Seq Scan on file_info_7 (cost=0.00..11762.44 rows=29502
width=114) (actual time=0.244..2533.388 rows=25795 loops=1)
Filter: ((file_type)::text = 'd'::text)
Total runtime: 13042.421 ms
(5 rows)
Since my last post I went back to a query closer to what I actually
want. What is most important to me is that 'file_parent_dir, file_name,
file_display' are returned and that the results are sorted by
'file_parent_dir, file_name' and the results are restricted to where
'file_info='d''.
Basically what I am trying to do is display a directory tree in a
file browser. I had this working before but it was far, far too slow
once the number of directories to display got much higher than 1,000.
That is what 'file_display' is, by the way.
Again, thank you!
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: <42AD0B94.9090508@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