Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WkIuW-0005gF-EK for pgsql-performance@arkaria.postgresql.org; Tue, 13 May 2014 20:02:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WkIuV-0002Km-UU for pgsql-performance@arkaria.postgresql.org; Tue, 13 May 2014 20:02:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WkIuV-0002Kg-35 for pgsql-performance@postgresql.org; Tue, 13 May 2014 20:02:47 +0000 Received: from mail-wg0-x233.google.com ([2a00:1450:400c:c00::233]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WkIuS-0004Ir-DT for pgsql-performance@postgresql.org; Tue, 13 May 2014 20:02:46 +0000 Received: by mail-wg0-f51.google.com with SMTP id x13so916060wgg.22 for ; Tue, 13 May 2014 13:02:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=user-agent:date:subject:from:to:message-id:thread-topic :mime-version:content-type; bh=kUfKYnhfdDn9O+qablMjXqlxSuc9Mm3IZkESsovduu0=; b=d1mhkN9khHeC8nTSgjuGyEpoctGVOdaG5qXf5hce1K2B93X6LdjfC4+lcv7NplCDOw GXNPY++DxozLkDvLX7MOKEDH4UDcTac0O1a34QVSaLDFv7tlF1fE0o/JM/EsW+juN/hW AnzL0NxTEDdAFqEgAvPQhDc/wX1WLuOBSjLieRMyhzP/DEIa/KRLuwSIp7Ld7lWyLN4C JbY6ZpNcea+x0Nyip++erbRB94nA6ANT4/AjS6sRgXU9Lcw88IW4KRamu+G0MDZZHyQ+ GHc4m5QBiUZgLUsVYCpYPHQnOY5OuiuU9orID/6pYbiw3wF8pFIZ1YpjkOv+RLPcnLRD uBog== X-Received: by 10.180.183.131 with SMTP id em3mr11433901wic.56.1400011363356; Tue, 13 May 2014 13:02:43 -0700 (PDT) Received: from [192.168.0.28] (cpc14-dals19-2-0-cust193.20-2.cable.virginm.net. [62.31.88.194]) by mx.google.com with ESMTPSA id lj15sm23628596wic.13.2014.05.13.13.02.34 for (version=TLSv1 cipher=RC4-SHA bits=128/128); Tue, 13 May 2014 13:02:42 -0700 (PDT) User-Agent: Microsoft-MacOutlook/14.4.1.140326 Date: Tue, 13 May 2014 21:02:16 +0100 Subject: Constraint exclusion won't exclude parent table From: Tim Kane To: "pgsql-performance@postgresql.org" Message-ID: Thread-Topic: Constraint exclusion won't exclude parent table Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3482859760_69591911" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3482859760_69591911 Content-type: text/plain; charset="UTF-8" Content-transfer-encoding: quoted-printable Hi all, First some background. I have inherited a system that appears to have a lot of logic built into views upon views upon views (and then some more views for good measure). It struck me that the CASE conditions built into those views are causing poorer performance than expected =E2=80=93 so I thought I would run a few tests against the base tables see where the difference lies. Anyway, that=E2=80=99s all by the by.. Because what I found on my travels is that the parent table of the relevant partitions is being included and appended in the query plan. This is all documented and I understand why, fine. But the impact of this is greater than I was expecting. For instance, if I query the partition directly (for all tuples it contains= ) versus a query that targets the same partition via exclusion rules - I find the direct query runs in less than half the time. Direct query: Seq Scan on partitioned.ts_201405 track_streams (cost=3D0.00..4167467.56 rows=3D65067252 width=3D253) (actual time=3D0.010..96796.053 rows=3D65328073 loops=3D1) Output:=20 Filter:=20 Buffers: shared hit=3D354 read=3D2215096 Total runtime: 137437.675 ms (5 rows) Indirect query: Result (cost=3D0.00..4167467.56 rows=3D65067253 width=3D253) (actual time=3D0.011..250057.941 rows=3D65328073 loops=3D1) Output: Buffers: shared hit=3D322 read=3D2215128 -> Append (cost=3D0.00..4167467.56 rows=3D65067253 width=3D253) (actual time=3D0.010..163452.326 rows=3D65328073 loops=3D1) Buffers: shared hit=3D322 read=3D2215128 -> Seq Scan on archive.ts (cost=3D0.00..0.00 rows=3D1 width=3D199) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Output: Filter: -> Seq Scan on partitioned.ts_201405 (cost=3D0.00..4167467.56 rows=3D65067252 width=3D253) (actual time=3D0.006..85883.925 rows=3D65328073 loops=3D1) Output: Filter: Buffers: shared hit=3D322 read=3D2215128 Total runtime: 289238.187 ms (13 rows) So what is the append node actually doing, and why is it necessary? I expect that it simply does what it says, and appends the results of those two seq-scans. But in reality, there isn=E2=80=99t a lot to do there. While I expect a little bit of overhead, surely it just passes the tuples straight through to the result node and that will be that.. No? (yeah, I=E2=80=99ve made a few assumptions/guesses here, but I=E2=80=99m not sure I=E2=80=99m= ready to look at the code just yet) Cheers, Tim --B_3482859760_69591911 Content-type: text/html; charset="UTF-8" Content-transfer-encoding: quoted-printable
Hi all,

First some background.
I have inherited a system that app= ears to have a lot of logic built into views upon views upon views (and then= some more views for good measure).
It struck me that the CASE con= ditions built into those views are causing poorer performance than expected = – so I thought I would run a few tests against the base tables see whe= re the difference lies.

Anyway, that’s all by= the by.. Because what I found on my travels is that the parent table of the= relevant partitions is being included and appended in the query plan.
=
This is all documented and I understand why, fine.  But the impact= of this is greater than I was expecting.

For insta= nce, if I query the partition directly (for all tuples it contains) versus a= query that targets the same partition via exclusion rules - I find the dire= ct query runs in less than half the time.


Direct query:
 Seq Scan on partitioned.ts_201405 track_streams &nbs= p;(cost=3D0.00..4167467.56 rows=3D65067252 width=3D253) (actual time=3D0.010..96796.= 053 rows=3D65328073 loops=3D1)
  &nb= sp;Output: 
   Filter:=  
   Buffers: shared h= it=3D354 read=3D2215096
 Total runti= me: 137437.675 ms
(5 rows)

Indirect query:
 Result  (cost=3D0.00..4167467.56 rows=3D65067253 width=3D25= 3) (actual time=3D0.011..250057.941 rows=3D65328073 loops=3D1)
   Output:
   Buffers: shared hit=3D322 read=3D2215128
   ->  Append  (cost=3D0.00..4167467.5= 6 rows=3D65067253 width=3D253) (actual time=3D0.010..163452.326 rows=3D65328073 loop= s=3D1)
        &nbs= p;Buffers: shared hit=3D322 read=3D2215128
         ->  Seq Scan on archive.ts  = ;(cost=3D0.00..0.00 rows=3D1 width=3D199) (actual time=3D0.001..0.001 rows=3D0 loops=3D1= )
          =      Output: 
&nb= sp;              Filter:
         ->  Seq Sca= n on partitioned.ts_201405  (cost=3D0.00..4167467.56 rows=3D65067252 width=3D= 253) (actual time=3D0.006..85883.925 rows=3D65328073 loops=3D1)
<= font face=3D"Consolas">               = Output: 
      &n= bsp;        Filter:
               Buffers: shared hi= t=3D322 read=3D2215128
 Total runtim= e: 289238.187 ms
(13 rows)


So what is the append=  node actually doing, and why is it necessary?  
I expec= t that it simply does what it says, and appends the results of those two seq= -scans.  But in reality, there isn’t a lot to do there. While I e= xpect a little bit of overhead, surely it just passes the tuples straight th= rough to the result node and that will be that.. No?

(yeah, I’ve made a few assumptions/guesses here, but I’m not s= ure I’m ready to look at the code just yet)

<= br>
Cheers,

Tim

=







--B_3482859760_69591911--