pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: On partitioning
Date: Tue, 16 Dec 2014 18:01:15 -0800
Message-ID: <5490E3EB.8020904@agliodbs.com> (raw)
In-Reply-To: <WM!b18eb67c4aa52f89deaf07f899a368eb286454c2a91eb4369b34119763798b42effdcbeaebf0622b348e80940cb98ea5!@asav-2.01.com>
References: <CAA4eK1LcDLmOfw3Y4L9FeAn+pMcLg6OLSTK45G9SBQUvO4tZYQ@mail.gmail.com>
	<CA+TgmobH--E+GkGHsprjN-e_4-YR4208pmE9zp90sEjBjDBDMg@mail.gmail.com>
	<CAGTBQpaqEbSO+=s1xod1G6xfawc1RRnYEL3jGp870h7APnCgGg@mail.gmail.com>
	<9766.1418422242@sss.pgh.pa.us>
	<WM!5674e59aab89034f672b7668897d87b46824a54cca29a62ba08c1af0387796e192e949d0345717737e6887fc6cabf78c!@asav-3.01.com>
	<548B6EE3.3020309@agliodbs.com>
	<CAGTBQpby6DG1E1T+9wfGzMat46OWCxmQ5h-AEr6YzA=iYd5dbQ@mail.gmail.com>
	<20141213020946.GG1768@alvh.no-ip.org>
	<001601d0180c$a212f550$e638dff0$@lab.ntt.co.jp>
	<CA+TgmoaA18DL8SG51tDQ=wjQBAqb658cR1TSV3diLmk_x9iRzQ@mail.gmail.com>
	<WM!8cffd9c1cee9a4329fc6359e77402db760b5f8d1643541d65e3238e233ee747cb8155136b36d574ace2fb276dac2a8b2!@asav-3.01.com>
	<54907DD9.7060306@agliodbs.com>
	<CA+TgmobPx8NTOES2SRd2EfpzoiXaR2FXYNUP9RKwNUEyu+Y0sw@mail.gmail.com>
	<WM!b18eb67c4aa52f89deaf07f899a368eb286454c2a91eb4369b34119763798b42effdcbeaebf0622b348e80940cb98ea5!@asav-2.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

On 12/16/2014 05:52 PM, Robert Haas wrote:
> But in a more complicated case where the value there isn't known until
> runtime, yeah, it scans everything.  I'm not sure what the best way to
> fix that is.  If the partition bounds were stored in a structured way,
> as we've been discussing, then the Append or Merge Append node could,
> when initialized, check which partition the id = X qual routes to and
> ignore the rest.  But that's more iffy with the current
> representation, I think.

Huh.  I was just testing:

WHERE event_time BETWEEN timestamptz '2014-12-01' and ( timestamptz
'2014-12-01' + interval '1 month')

In that case, the expression above got folded to constants by the time
Postgres did the index scans, but it scanned all partitions.  So somehow
(timestamptz + interval) doesn't get constant-folded until after
planning, at least not on 9.3.

And of course this leaves out common patterns like "now() - interval '30
days'" or "to_timestamp('20141201','YYYYMMDD')"

Anyway, what I'm saying is that I personally regard the inability to
handle even moderately complex expressions a major failing of our
existing partitioning scheme (possibly its worst single failing), and I
would regard any new partitioning feature which didn't address that
issue as suspect.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers



view thread (169+ messages)  latest in thread

Message-ID: <5490E3EB.8020904@agliodbs.com>
Permalink:  ../5490E3EB.8020904@agliodbs.com/
Also on:    postgresql.org/message-id/5490E3EB.8020904@agliodbs.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: josh@agliodbs.com, robertmhaas@gmail.com, Langote_Amit_f8@lab.ntt.co.jp, alvherre@2ndquadrant.com, klaussfreire@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: On partitioning
  In-Reply-To: <5490E3EB.8020904@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox