Re: Index ot being used - Mailing list pgsql-performance

On Mon, Jun 13, 2005 at 00:29:08 -0400,
  Madison Kelly <linux@alteeve.com> wrote:
> 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''.

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.