Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B5BC2633525 for ; Tue, 2 Feb 2010 05:49:33 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 32996-01 for ; Tue, 2 Feb 2010 09:49:18 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 0B993632D3A for ; Tue, 2 Feb 2010 05:49:23 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.3/8.14.3) with ESMTP id o129nJpJ079458 for ; Tue, 2 Feb 2010 05:49:19 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id o129mIcQ077080 for pgsql-performance@postgresql.org; Tue, 2 Feb 2010 05:48:18 -0400 (AST) (envelope-from news) From: "Davor J." X-Newsgroups: pgsql.performance Subject: queries with subquery constraints on partitioned tables not optimized? Date: Tue, 2 Feb 2010 10:48:08 +0100 Organization: Hub.Org Networking Services Lines: 22 Message-ID: X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5843 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.079 tagged_above=-10 required=5 tests=AWL=-0.078, BAYES_50=0.001, SUBJECT_FUZZY_TION=0.156 X-Spam-Level: X-Archive-Number: 201002/42 X-Sequence-Number: 37463 Let's say you have one partitioned table, "tbl_p", partitioned according to the PK "p_pk". I have made something similar with triggers, basing myself on the manual for making partitioned tables. According to the manual, optimizer searches the CHECKs of the partitions to determine which table(s) to use (if applicable). So if one has CHECKs of kind "p_pk = some number", queries like "SELECT * from tbl_p where p_pk = 1" will only be searched in the appropriate table. One can check this with EXPLAIN. So far so good. Now, if one takes a subquery for "1", the optimizer evaluates it first (let's say to "1"), but then searches for it (sequentially) in every partition, which, for large partitions, can be very time-consuming and goes beyond the point of partitioning. Is this normal, or am I missing something? Kind regards, Davor