Skip site navigation (1) Skip section navigation (2)

Re: A Guide to Constraint Exclusion (Partitioning)

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, bizgres-general(at)pgfoundry(dot)org,pgsql-hackers(at)postgresql(dot)org
Subject: Re: A Guide to Constraint Exclusion (Partitioning)
Date: 2005-07-23 16:37:56
Message-ID: 87ackdbhqz.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Simon Riggs <simon(at)2ndquadrant(dot)com> writes:

> It's very common to scan whole ranges of dates on a large table, so in
> those cases you are really just maintaining the indexes for partitioning
> purposes. On older data it may be desirable not to have lots of indexes,
> or at least use their resources on the indexes they really do want.
> 
> Also, if you have a List partitioned table where all rows in that table
> have a single value, then you maintain an index for no reason other than
> partitioning. Thats an expensive waste. 
> 
> Simply put, adding a constraint is faster and cheaper than adding an
> pointless index. CE gives people that option.

Note also that the index is only useful if the index is *being used*. And
index scans are much slower than sequential scans.

So a query like "select * from invoices where fiscal_year = ?" is best
implemented by doing a sequential scan across invoices_fy05. This is *much*
faster than using indexes even if the indexes manage to speed up the empty
partitions simply because an index scan across the full partition would be so
much slower than a sequential scan.

-- 
greg


In response to

Responses

pgsql-hackers by date

Next:From: Joshua D. DrakeDate: 2005-07-23 16:43:06
Subject: Re: [HACKERS] Enticing interns to PostgreSQL
Previous:From: Jim C. NasbyDate: 2005-07-23 16:24:06
Subject: Re: [HACKERS] Enticing interns to PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group