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

help / color / mirror / Atom feed
From: Greg Smith <greg@2ndquadrant.com>
To: Vincenzo Romano <vincenzo.romano@notorand.it>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: On Scalability
Date: Thu, 07 Oct 2010 10:32:38 -0400
Message-ID: <4CADDA06.5070002@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTi=ZH-bBoXhsMf0Nj9xxmjoTCTj5Sjgk1cR1h-SH@mail.gmail.com>
References: <AANLkTi=6=WRpND-MXv92D-+iC9AnYDwLsbbXMXj4uNky@mail.gmail.com>
	<1280423526.8613.36.camel@jd-desktop.unknown.charter.com>
	<AANLkTikV96eAFxEuqk4k6jadtDomLhnJrwqtEh3mNKs6@mail.gmail.com>
	<1280425184.8613.41.camel@jd-desktop.unknown.charter.com>
	<AANLkTim7HQQz=qGfW=xDhx-8HNppYxXF48NjnU2-XiNt@mail.gmail.com>
	<1280427410.8613.44.camel@jd-desktop.unknown.charter.com>
	<AANLkTimSGghp4TseAV-zgNYakkyH3J_jW2k8peL7EOTv@mail.gmail.com>
	<4CAD769D.9090301@enterprisedb.com>
	<1286437290.2304.192.camel@ebony>
	<4CAD7BED.4050009@enterprisedb.com>
	<AANLkTi==OD9afGEDOm7OY3KYydLXcMj=kO7M5+QFbaM0@mail.gmail.com>
	<AANLkTiktPBVfa950ntGVDroY8ZNR1npQaA-oPnAJSn7n@mail.gmail.com>
	<AANLkTi=ZH-bBoXhsMf0Nj9xxmjoTCTj5Sjgk1cR1h-SH@mail.gmail.com>

Vincenzo Romano wrote:
> I see the main problem in the way the planner "understands" which partition
> is useful and which one is not.
> Having the DDL supporting the feature could just be syntactic sugar
> if the underlying mechanism is inadequate.
>   

You have the order of this backwards.  In order to do better than the 
way the current scheme is implemented, the optimizer needs higher 
quality metadata about the structure of the partitions to work with.  
Right now, it's inferring them from the CHECK constraints, which 
requires the whole theorem-proving bit Tom mentioned.  That's never 
going to get any more algorithmically efficient than it already is.

If the DDL that created the partitions also made better quality metadata 
available about the structure of the partitions, at that point it would 
be possible to also do better in how the optimizer pruned partitions to 
consider too.  If the list it has was known to be in a particular 
structured/sorted order, the optimizer could do a binary search to find 
relevant partitions, rather than the linear scan required right now.

Until that work is done, any other improvement attempts are doomed to 
fail.  That's the point Robert was trying to make to you.  And the fact 
Oracle does this is why it's able to scale to high partition counts 
better than PostgreSQL can.

You can read more about the work that was being done here at 
http://wiki.postgresql.org/wiki/Table_partitioning

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us





view thread (45+ messages)  latest in thread

Message-ID: <4CADDA06.5070002@2ndquadrant.com>
Permalink:  ../4CADDA06.5070002@2ndquadrant.com/
Also on:    postgresql.org/message-id/4CADDA06.5070002@2ndquadrant.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-performance@postgresql.org
  Cc: greg@2ndquadrant.com, vincenzo.romano@notorand.it, robertmhaas@gmail.com, heikki.linnakangas@enterprisedb.com, simon@2ndquadrant.com, pgsql-hackers@postgresql.org
  Subject: Re: On Scalability
  In-Reply-To: <4CADDA06.5070002@2ndquadrant.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