Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A99649FBAD4 for ; Fri, 30 Mar 2007 06:33:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 93115-07 for ; Fri, 30 Mar 2007 06:33:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227]) by postgresql.org (Postfix) with ESMTP id 7AE769FB9E5 for ; Fri, 30 Mar 2007 06:33:44 -0300 (ADT) Received: from mail.mm.eutelsat.org (localhost [127.0.0.1]) by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id l2U9WUU06349 for ; Fri, 30 Mar 2007 11:32:31 +0200 Received: from [127.0.0.1] (accesspoint.mm.eutelsat.org [194.214.173.4]) by mail.mm.eutelsat.org (8.13.1/8.13.1) with ESMTP id l2U9aGp4012150 for ; Fri, 30 Mar 2007 11:36:17 +0200 Message-ID: <460CD941.6080303@bigfoot.com> Date: Fri, 30 Mar 2007 11:32:49 +0200 From: Gaetano Mendola User-Agent: Thunderbird 2.0.0.0pre (Windows/20070328) MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: Wrong plan sequential scan instead of an index one X-Enigmail-Version: 0.95b Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.7, clamav-milter version 0.88.7 on mail.mm.eutelsat.org X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/436 X-Sequence-Number: 23841 -----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=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 -----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-----