Re: Unexplainable slow down...

[snyder(at)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(at)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(at)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(at)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(at)megazone23(dot)bigpanda(dot)com]
> Sent: Thursday, March 14, 2002 7:38 AM
> To: Ron Snyder
> Cc: 'pgsql-general(at)postgresql(dot)org'
> Subject: RE: [GENERAL] Unexplainable slow down...
>
>
> On Wed, 13 Mar 2002, Ron Snyder wrote:
>
> >
> >
> > > -----Original Message-----
> > > From: Stephan Szabo [mailto:sszabo(at)megazone23(dot)bigpanda(dot)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).
>