X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id ECDC19FA5C8 for ; Thu, 8 Jun 2006 08:41:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 20257-01 for ; Thu, 8 Jun 2006 08:40:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186]) by postgresql.org (Postfix) with ESMTP id 5D3A09F9EF3 for ; Thu, 8 Jun 2006 08:40:45 -0300 (ADT) Received: from [84.143.10.74] (helo=pse.dyndns.org) by mrelayeu.kundenserver.de (node=mrelayeu8) with ESMTP (Nemesis), id 0ML2ov-1FoIsV49N9-0000nk; Thu, 08 Jun 2006 13:40:44 +0200 Received: from [192.168.0.103] (helo=[192.168.0.103]) by pse.dyndns.org with esmtp (Exim 4.44) id 1FoIsU-0006ml-Rn for pgsql-performance@postgresql.org; Thu, 08 Jun 2006 13:40:42 +0200 Message-ID: <44880CB1.90307@pse-consulting.de> Date: Thu, 08 Jun 2006 13:40:33 +0200 From: Andreas Pflug User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: JOIN with inherited table ignores indexes Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: kundenserver.de abuse@kundenserver.de login:0ce7ee5c3478b8d72edd8e05ccd40b70 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/85 X-Sequence-Number: 19442 I have this table setup on a 8.1.4 server: pj_info_attach(attachment_nr, some more cols) -- index, 50k rows pj_info_attach_compressable() INHERITS (pj_info_attach) -- index, 1M rows pj_info_attach_not_compressable() INHERITS (pj_info_attach) -- index, 0 rows EXPLAIN ANALYZE SELECT aes FROM pj_info_attach WHERE attachment_nr in (.. 20 numeric key values.. ) yields a big bitmap index scan plan, 1.8ms total runtime, that's fine. Using a subselect on zz_attachment_graustufentest, which has 20 rows of exactly the key values entered manually in the query above: EXPLAIN ANALYZE SELECT aes FROM pj_info_attach WHERE attachment_nr in (SELECT attachment_nr FROM zz_attachment_graustufentest) gives 49s runtime, and full table scans. Merge Join (cost=158472.98..164927.22 rows=107569 width=8) (actual time=49714.702..49715.142 rows=20 loops=1) Merge Cond: ("outer"."?column2?" = "inner"."?column3?") -> Sort (cost=2.16..2.21 rows=20 width=13)(actual time=0.752..0.830 rows=20 loops=1) Sort Key: (zz_attachment_graustufentest.attachment_nr)::numeric -> Result (cost=1.63..1.73 rows=20 width=13) (actual time=0.220..0.637 rows=20 loops=1) -> Unique (cost=1.63..1.73 rows=20 width=13) (actual time=0.210..0.459 rows=20 loops=1) -> Sort (cost=1.63..1.68 rows=20 width=13) (actual time=0.202..0.281 rows=20 loops=1) Sort Key: zz_attachment_graustufentest.attachment_nr -> Seq Scan on zz_attachment_graustufentest (cost=0.00..1.20 rows=20 width=13) (actual time=0.007..0.092 rows=20 loops=1) -> Sort (cost=158470.81..161160.04 rows=1075690 width=40) (actual time=44705.196..47222.685 rows=589842 loops=1) Sort Key: (public.pj_info_attach.attachment_nr)::numeric -> Result (cost=0.00..32736.90 rows=1075690 width=40) (actual time=0.023..21958.761 rows=1074930 loops=1) -> Append (cost=0.00..32736.90 rows=1075690 width=40) (actual time=0.015..13485.153 rows=1074930 loops=1) -> Seq Scan on pj_info_attach (cost=0.00..1433.57 rows=49957 width=21) (actual time=0.008..214.308 rows=49957 loops=1) -> Seq Scan on pj_info_attach_compressable pj_info_attach (cost=0.00..31285.73 rows=1024973 width=21) (actual time=0.032..4812.090 rows=1024973 loops=1) -> Seq Scan on pj_info_attach_not_compressable pj_info_attach (cost=0.00..17.60 rows=760 width=40) (actual time=0.005..0.005 rows=0 loops=1) Total runtime: 49747.630 ms Any explanation for this horror? Regards, Andreas