Wrong plan sequential scan instead of an index one

-----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=877682loops=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=7801loops=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.050rows=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-----