Tom Lane wrote:
Richard Huxton <dev@archonet.com> writes:
Ah, but it's got no way of knowing what matches you'll get for
'%anything%'. There's no easy way to get statistics for matching substrings.
8.2 actually tries the match on the most-common-values list, if said
list is big enough (I think the threshold is stats target = 100).
Not sure if that will help here, though.
I didn't change the stats target and I obtain on a 8.2 engine the result I
was expecting.


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=163228.76..163228.77 rows=1 width=8) (actual time=23.398..23.398 rows=1 loops=1)
-> Nested Loop (cost=74.71..163020.31 rows=83380 width=8) (actual time=2.237..18.580 rows=7801 loops=1)
-> HashAggregate (cost=2.22..2.41 rows=19 width=4) (actual time=0.043..0.045 rows=1 loops=1)
-> Seq Scan on l_pvcp (cost=0.00..2.17 rows=19 width=4) (actual time=0.028..0.037 rows=1 loops=1)
Filter: (value ~~* '%pi%'::text)
-> Bitmap Heap Scan on t_oa_2_00_card (cost=72.49..8525.04 rows=4388 width=12) (actual time=2.188..9.204 rows=7801 loops=1)
Recheck Cond: (t_oa_2_00_card.pvcp = l_pvcp.id)
-> Bitmap Index Scan on i3_t_oa_2_00_card (cost=0.00..71.39 rows=4388 width=0) (actual time=1.768..1.768 rows=7801 loops=1)
Index Cond: (t_oa_2_00_card.pvcp = l_pvcp.id)
Total runtime: 23.503 ms
(10 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=38343.44..38343.45 rows=1 width=8) (actual time=23.386..23.387 rows=1 loops=1)
-> Nested Loop (cost=76.52..38299.55 rows=17554 width=8) (actual time=2.246..18.576 rows=7801 loops=1)
-> HashAggregate (cost=2.18..2.22 rows=4 width=4) (actual time=0.041..0.043 rows=1 loops=1)
-> Seq Scan on l_pvcp (cost=0.00..2.17 rows=4 width=4) (actual time=0.026..0.035 rows=1 loops=1)
Filter: (value ~~* 'pi'::text)
-> Bitmap Heap Scan on t_oa_2_00_card (cost=74.33..9519.48 rows=4388 width=12) (actual time=2.198..9.161 rows=7801 loops=1)
Recheck Cond: (t_oa_2_00_card.pvcp = l_pvcp.id)
-> Bitmap Index Scan on i3_t_oa_2_00_card (cost=0.00..73.24 rows=4388 width=0) (actual time=1.779..1.779 rows=7801 loops=1)
Index Cond: (t_oa_2_00_card.pvcp = l_pvcp.id)
Total runtime: 23.491 ms
(10 rows)


I had to lower the random_page_cost = 2.5 in order to avoid the sequential scan on the big table t_oa_2_00_card.

this is a +1 to update our engines to a 8.2.


Regards
Gaetano Mendola

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 15 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