pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizer not using index when it should
3+ messages / 3 participants
[nested] [flat]
* Optimizer not using index when it should
@ 2003-04-29 09:03 Rob Messer <rrmesser@yahoo.com>
2003-04-29 13:04 ` Re: Optimizer not using index when it should Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Rob Messer @ 2003-04-29 09:03 UTC (permalink / raw)
To: pgsql-performance
Like some other recent messages, this one is about getting postgresql
to use an index that it seems it should clearly use. (But this one has
nothing to do with count(*)).
Here is my table:
Column | Type | Modifiers
--------------+-----------------------------+-----------
dsid | character varying(20) | not null
recid | numeric(8,0) | not null
trans_id | character varying(16) | not null
status | character varying(1) |
init_ts | timestamp without time zone |
last_ts | timestamp without time zone |
last_form_id | character varying(8) |
secval | character varying(20) |
Indexes: ds_rec1 unique btree (recid),
ds_rec2 btree (dsid)
Here is my version info:
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
Currently the table ds_record has about 250,000 records in it. Of
those, about 3000 have dsid = 'starz'. When I need to look up all the
recids with this dsid in ds_record, I have the following simple query:
select recid from ds_record where dsid = 'startz';
But it doesn't use the index ds_rec2 on dsid. Here is the explain
analyze output:
intellis2=> explain analyze select recid from ds_record where dsid =
'starz';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on ds_record (cost=0.00..6186.21 rows=3484 width=12) (actual
time=10.60..408.12 rows=3484 loops=1)
Filter: (dsid = 'starz'::character varying)
Total runtime: 410.14 msec
(3 rows)
but if I turn off seqscan I get this:
intellis2=> set enable_seqscan=off;
SET
intellis2=> explain analyze select recid from ds_record where dsid =
'starz';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using ds_rec2 on ds_record (cost=0.00..7185.47 rows=3484
width=12)
(actual time=0.17..12.94 rows=3484 loops=1)
Index Cond: (dsid = 'starz'::character varying)
Total runtime: 14.97 msec
(3 rows)
so it is faster by more than a factor of 25 to use the index. The
problem gets worse when I add a join to the table.
I have tried the following:
alter table ds_record alter dsid set statistics 1000;
vacuum analyze ds_record;
drop index ds_rec2;
CREATE INDEX ds_rec2 ON ds_record USING btree (dsid);
But to no avail, I get the same results.
Interestingly, for queries that return fewer rows it does use the
correct index. For example, dsid="mapbuy2" appears about 500 times in
ds_record. Here is the explain out there (with enable_seqscan back
on):
intellis2=> explain analyze select recid from ds_record where dsid =
'mapbuy2';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using ds_rec2 on ds_record (cost=0.00..1351.17 rows=522
width=12) (actual time=0.18..4.31 rows=522 loops=1)
Index Cond: (dsid = 'mapbuy2'::character varying)
Total runtime: 4.68 msec
To me it seems that the threshold for doing a table scan is wrong --
when the rows retrieved are about 1.25% of the table it does a scan.
What can I do to fix this -- is there something I am missing about
setting statistics or some configuration variable I can change? Any
insights would be greatly appreciated. Thank you,
Rob Messer
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Optimizer not using index when it should
2003-04-29 09:03 Optimizer not using index when it should Rob Messer <rrmesser@yahoo.com>
@ 2003-04-29 13:04 ` Rod Taylor <rbt@rbt.ca>
2003-04-29 14:18 ` Re: Optimizer not using index when it should Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Rod Taylor @ 2003-04-29 13:04 UTC (permalink / raw)
To: Rob Messer <rrmesser@yahoo.com>; +Cc: pgsql-performance
> What can I do to fix this -- is there something I am missing about
> setting statistics or some configuration variable I can change? Any
> insights would be greatly appreciated. Thank you,
If you look at the estimates for cost, the index scan is more expensive
by ~1/8th. But as you've shown, it's not.
You might try adjusting the random_page_cost down to something more
appropriate for your hardware and situation.
Do testing... this may cause other queries to use an index scan when
they should have been doing a sequential scan. Mistakenly using an
index can be a much more costly error (hence the high default
random_page_cost).
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Optimizer not using index when it should
2003-04-29 09:03 Optimizer not using index when it should Rob Messer <rrmesser@yahoo.com>
2003-04-29 13:04 ` Re: Optimizer not using index when it should Rod Taylor <rbt@rbt.ca>
@ 2003-04-29 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-04-29 14:18 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Rob Messer <rrmesser@yahoo.com>; pgsql-performance
Rod Taylor <rbt@rbt.ca> writes:
>> What can I do to fix this -- is there something I am missing about
>> setting statistics or some configuration variable I can change? Any
>> insights would be greatly appreciated. Thank you,
> You might try adjusting the random_page_cost down to something more
> appropriate for your hardware and situation.
Also, is the table physically ordered by dsid? If so, is that condition
likely to persist? You may be looking at a test-condition artifact
here --- a poor estimate for an ordered table may not mean much when
you get to realistic database states.
I assume you've done an ANALYZE of course --- what does the pg_stats row
for column dsid contain?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-29 14:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-29 09:03 Optimizer not using index when it should Rob Messer <rrmesser@yahoo.com>
2003-04-29 13:04 ` Rod Taylor <rbt@rbt.ca>
2003-04-29 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox