Re: Wrong plan sequential scan instead of an index one [8.2 solved it] - Mailing list pgsql-performance

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

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=7801loops=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.768rows=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=7801loops=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.779rows=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-----