Re: Wrong plan sequential scan instead of an index one [8.2 solved it]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGEN237UpzwH2SGd4RAo9yAJ9K7bTa5eEUjvPjk/OcAMgt+AncmQCfbkBH
FlomqoY1ASv3TDkd9L5hgG4=
=ZLS8
-----END PGP SIGNATURE-----