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

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 15 | next ›
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedMar 30, '07 at 9:33a
activeApr 2, '07 at 10:42a
posts15
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase