pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Gaetano Mendola <mendola@bigfoot.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Wrong plan sequential scan instead of an index one
Date: Fri, 30 Mar 2007 11:32:49 +0200
Message-ID: <460CD941.6080303@bigfoot.com> (raw )
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi all,
take a look at those plans:
test=# explain analyze SELECT COUNT(id) FROM t_oa_2_00_card WHERE pvcp in (select id from l_pvcp where value ilike '%pi%');
QUERY PLAN
- ---------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=154279.01..154279.01 rows=1 width=8) (actual time=4010.094..4010.096 rows=1 loops=1)
-> Hash IN Join (cost=2.22..153835.49 rows=177404 width=8) (actual time=2.908..4001.814 rows=7801 loops=1)
Hash Cond: ("outer".pvcp = "inner".id)
-> Seq Scan on t_oa_2_00_card (cost=0.00..147670.82 rows=877682 width=12) (actual time=0.030..2904.522 rows=877682 loops=1)
-> Hash (cost=2.17..2.17 rows=19 width=4) (actual time=0.093..0.093 rows=1 loops=1)
-> Seq Scan on l_pvcp (cost=0.00..2.17 rows=19 width=4) (actual time=0.066..0.081 rows=1 loops=1)
Filter: (value ~~* '%pi%'::text)
Total runtime: 4010.413 ms
(8 rows)
test=# explain analyze SELECT COUNT(id) FROM t_oa_2_00_card WHERE pvcp in (select id from l_pvcp where value ilike 'pi');
QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=93540.82..93540.83 rows=1 width=8) (actual time=55.333..55.334 rows=1 loops=1)
-> Nested Loop (cost=84.60..93447.44 rows=37348 width=8) (actual time=2.730..46.770 rows=7801 loops=1)
-> HashAggregate (cost=2.18..2.22 rows=4 width=4) (actual time=0.089..0.092 rows=1 loops=1)
-> Seq Scan on l_pvcp (cost=0.00..2.17 rows=4 width=4) (actual time=0.065..0.081 rows=1 loops=1)
Filter: (value ~~* 'pi'::text)
-> Bitmap Heap Scan on t_oa_2_00_card (cost=82.42..23216.95 rows=11548 width=12) (actual time=2.633..29.566 rows=7801 loops=1)
Recheck Cond: (t_oa_2_00_card.pvcp = "outer".id)
-> Bitmap Index Scan on i3_t_oa_2_00_card (cost=0.00..82.42 rows=11548 width=0) (actual time=2.050..2.050 rows=7801 loops=1)
Index Cond: (t_oa_2_00_card.pvcp = "outer".id)
Total runtime: 55.454 ms
(10 rows)
Isn't too much choose a sequential scan due to 19 estimated rows when with 4 estimated does a correct index scan ?
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGDNlB7UpzwH2SGd4RAjY8AJ9yrIaQe297m3Lh7+ZVM4i9hoqlYQCeJFGL
z00RLwJ5yR/7bOT2TVx+JVA=
=1lOI
-----END PGP SIGNATURE-----
view thread (15+ messages) latest in thread
Message-ID: <460CD941.6080303@bigfoot.com>
Permalink: ../460CD941.6080303@bigfoot.com/
Also on: postgresql.org/message-id/460CD941.6080303@bigfoot.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mendola@bigfoot.com
Subject: Re: Wrong plan sequential scan instead of an index one
In-Reply-To: <460CD941.6080303@bigfoot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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