Re: why am I getting a seq scan on this query?

Mark Harrison wrote:
> I'm expecting this to do an indexed scan... any clue why it's not?  This is
> with PG 7.4.

Someone might have a better idea but my guess is that PG things the
seq_scan would be faster. You could try decreasing your
random_page_cost. I have also heard that setting your (although I
haven't tested this) effective_cache_size higher then normal helps in
these scenarios but your mileage may vary.

Sincerely,

Joshua D. Drake



>
> Thanks!!
>
> planb=# explain select id,shotname from df_files where
> showid=30014515::bigint;
>                             QUERY PLAN
> -------------------------------------------------------------------
>  Seq Scan on df_files  (cost=0.00..791035.45 rows=540370 width=22)
>    Filter: (showid = 30014515::bigint)
> (2 rows)
>
>
>
> planb=# \d df_files;
>                Table "public.df_files"
>   Column   |            Type             | Modifiers
> -----------+-----------------------------+-----------
>  id        | bigint                      | not null
>  showid    | bigint                      | not null
>  shotname  | character varying(256)      | not null
>  elemname  | character varying(256)      | not null
>  frameno   | character varying(12)       | not null
>  ext       | character varying(12)       | not null
>  filename  | character varying(256)      | not null
>  filesize  | bigint                      |
>  locked    | boolean                     |
>  timestamp | timestamp without time zone |
> Indexes:
>     "df_files_pkey" primary key, btree (id)
>     "df_files_elemname" btree (elemname)
>     "df_files_ext" btree (ext)
>     "df_files_filename" btree (filename)
>     "df_files_frameno" btree (frameno)
>     "df_files_shotname" btree (shotname)
>     "df_files_show" btree (showid)
>     "df_files_showid" btree (showid)
>
> planb=# select count(*) from df_files where showid=30014515::bigint;
>  count
> --------
>  528362
> (1 row)
>
> Time: 420598.071 ms
> planb=# select count(*) from df_files;
>   count
> ----------
>  24415513
> (1 row)
>
> Time: 306554.085 ms
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend


--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/