-----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-----
