From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | Mark Harrison <mh(at)pixar(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: why am I getting a seq scan on this query? |
Date: | 2006-01-06 21:13:58 |
Message-ID: | 43BEDD96.8030309@commandprompt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Wes | 2006-01-06 21:26:43 | Re: ./configure --with-openssl=path fails |
Previous Message | Mark Harrison | 2006-01-06 21:09:37 | why am I getting a seq scan on this query? |