From: Madison Kelly <linux@alteeve.com>
To: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index ot being used
Date: Sun, 12 Jun 2005 10:12:27 -0400
Message-ID: <42AC42CB.8050703@alteeve.com> (raw)
In-Reply-To: <20050610155535.GO8451@tobias.nordicbet.com>
References: <1825.216.209.137.1.1118419819.squirrel@mail.alteeve.com>
<20050610155535.GO8451@tobias.nordicbet.com>
Tobias Brox wrote:
> [linux@alteeve.com - Fri at 12:10:19PM -0400]
>
>>tle-bu=> EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM
>>file_info_7;
>> QUERY PLAN
>>----------------------------------------------------------------------------------------------------------------------
>> Seq Scan on file_info_7 (cost=0.00..11028.35 rows=294035 width=118)
>>(actual time=0.122..2707.764 rows=294035 loops=1)
>> Total runtime: 3717.862 ms
>>(2 rows)
>>
>
>
> As far as I can see, you are selecting everything from the table without any
> sort order. The only rational thing to do then is a sequential scan, it's
> no point in an index scan.
>
Thanks for replying, Tobias and Jacques!
Doh! This is a case of over simplification, I think. I was trying to
simplify my query as much as I could and then work it out to the actual
query I want. It would seem I don't understand how to use indexes quite
right. Do you think you might be able to help me with a useful index?
Here is the 'file_info_7' schema, my query and the 'explain analyze'
results:
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_type, file_parent_dir, file_name)
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;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Sort (cost=14541.24..14603.48 rows=24895 width=118) (actual
time=15751.804..15967.591 rows=25795 loops=1)
Sort Key: file_parent_dir, file_name
-> Seq Scan on file_info_7 (cost=0.00..11763.44 rows=24895
width=118) (actual time=19.289..3840.845 rows=25795 loops=1)
Filter: ((file_type)::text = 'd'::text)
Total runtime: 16043.075 ms
(5 rows)
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"
Those are the three columns I am using in my restrictions so I
thought that would create an index this query would use. Do I need to do
something different because of the 'ORDER BY...'?
Thanks again for the replies!
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, tobias@nordicbet.com
Subject: Re: Index ot being used
In-Reply-To: <42AC42CB.8050703@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