Re: Unexplainable slow down... - Mailing list pgsql-general

[snyder@vault snyder]$ time psql quickview pgsql72  -c "select * from builds
where product='sourcepro_db' and state != 'N' and state != 'W' and finished
>= '03/12/2002' and finished < '03/13/2002' limit 15;" > test.out

real    1m5.387s
user    0m0.010s
sys     0m0.000s
[snyder@vault snyder]$ time psql quickview pgsql72  -c "set
enable_seqscan=off;select * from builds where product='sourcepro_db' and
state != 'N' and state != 'W' and finished >= '03/12/2002' and finished <
'03/13/2002' limit 15;" > test.out

real    0m31.689s
user    0m0.000s
sys     0m0.050s

[snyder@vault snyder]$ psql quickview pgsql72  -c "explain select * from
builds where product='sourcepro_db' and state != 'N' and state != 'W' and
finished >= '03/12/2002' and finished < '03/13/2002' limit 15;"
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..14629.38 rows=15 width=427)
  ->  Seq Scan on builds  (cost=0.00..133977.02 rows=137 width=427)

EXPLAIN
[snyder@vault snyder]$ psql quickview pgsql72  -c "set
enable_seqscan=off;explain select * from builds where product='sourcepro_db'
and state != 'N' and state != 'W' and finished >= '03/12/2002' and finished
< '03/13/2002' limit 15;"
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..22801.41 rows=15 width=427)
  ->  Index Scan using builds_product_state_finished on builds
(cost=0.00..208817.06 rows=137 width=427)

EXPLAIN

OK, I'm now more confused.  What do I do next to figure out why postgres
isn't choosing the better query?  We're running a vacuum analyze every
night-- do I need to tweak the weights so that seq_scan is less likely?

Here are the indices (I apologize for the formatting-- is there a different
format you'd prefer?):
quickview=# select * from pg_indexes where tablename='builds';
 tablename |            indexname            |
indexdef
-----------+---------------------------------+------------------------------
-------------------------------------------------------------------------
 builds    | builds_pkey                     | CREATE UNIQUE INDEX
builds_pkey ON builds USING btree (id)
 builds    | builds_product_os_compiler      | CREATE INDEX
builds_product_os_compiler ON builds USING btree (product, os, compiler)
 builds    | builds_product_state_finished   | CREATE INDEX
builds_product_state_finished ON builds USING btree (product, state,
finished)
 builds    | builds_product_state_submitted  | CREATE INDEX
builds_product_state_submitted ON builds USING btree (product, state,
submitted)
 builds    | builds_os                       | CREATE INDEX builds_os ON
builds USING btree (os)
 builds    | builds_compiler                 | CREATE INDEX builds_compiler
ON builds USING btree (compiler)
 builds    | builds_vis_machine_fin_state    | CREATE INDEX
builds_vis_machine_fin_state ON builds USING btree (visible, machine,
finished, state)
 builds    | builds_vis_os_fin_state         | CREATE INDEX
builds_vis_os_fin_state ON builds USING btree (visible, os, finished, state)
 builds    | builds_vis_compiler_fin_state   | CREATE INDEX
builds_vis_compiler_fin_state ON builds USING btree (visible, compiler,
finished, state)
 builds    | builds_machine                  | CREATE INDEX builds_machine
ON builds USING btree (machine)
 builds    | builds_vis_machine_submitted    | CREATE INDEX
builds_vis_machine_submitted ON builds USING btree (visible, machine,
submitted)
 builds    | builds_vis_os_submitted         | CREATE INDEX
builds_vis_os_submitted ON builds USING btree (visible, os, submitted)
 builds    | builds_vis_compiler_submitted   | CREATE INDEX
builds_vis_compiler_submitted ON builds USING btree (visible, compiler,
submitted)
 builds    | builds_started                  | CREATE INDEX builds_started
ON builds USING btree (started)
 builds    | builds_visible_product_submitte | CREATE INDEX
builds_visible_product_submitte ON builds USING btree (visible, product,
submitted)
 builds    | builds_vis_prod_fin_state       | CREATE INDEX
builds_vis_prod_fin_state ON builds USING btree (visible, product, finished,
state)
 builds    | builds_visible_product          | CREATE INDEX
builds_visible_product ON builds USING btree (visible, product)
 builds    | builds_product                  | CREATE INDEX builds_product
ON builds USING btree (product)
(18 rows)


-ron

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Thursday, March 14, 2002 7:38 AM
> To: Ron Snyder
> Cc: 'pgsql-general@postgresql.org'
> Subject: RE: [GENERAL] Unexplainable slow down...
>
>
> On Wed, 13 Mar 2002, Ron Snyder wrote:
>
> >
> >
> > > -----Original Message-----
> > > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> > > > Limit  (cost=0.00..833.71 rows=15 width=426)
> > > >   ->  Seq Scan on builds  (cost=0.00..123873.38 rows=2229
> > > > width=426)
> > > >
> > > > EXPLAIN
> > >
> > > What's the explain for limit 14? And what if you set
> > > enable_seqscan=off?
> >
> > The explain for limit 14 was the same (except that
> rows=14).  I know I
> > can disable seqscan in the .conf file-- is there a way that
> I can turn
> > that off in the psql client?  (It's a production database and they
> > (the users) have about 300 client connections that don't handle
> > disconnections very well.)
> set enable_seqscan=off;
> should do it.
>
> > > Some of these indices seem obvious others I'm not sure
> what they're
> > > supposed to cover.
> > >
> >
> > I agree, unfortunately I don't have a lot of information about the
> > indices.
> What does pg_indexes show for them (it'll get back a human
> readable index statement).
>