From: Greg Smith <greg@2ndquadrant.com>
To: Joseph Shraibman <jks@selectacast.net>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: index usage on queries on inherited tables
Date: Wed, 27 Apr 2011 22:18:37 -0400
Message-ID: <4DB8CE7D.8030503@2ndquadrant.com> (raw)
In-Reply-To: <4DB88690.5050701@selectacast.net>
References: <in36ut$24hs$1@news.hub.org>
<BANLkTi=g=dA7Bt_P=m0Amknp9r-k6x5-Sw@mail.gmail.com>
<4DB88690.5050701@selectacast.net>
Joseph Shraibman wrote:
> In a 52 gig table I have a "select id from table limit 1 order
> by id desc" returns instantly, but as soon as you declare a child table
> it tries to seq scan all the tables.
>
This is probably the limitation that's fixed in PostgreSQL 9.1 by this
commit (following a few others leading up to it):
http://archives.postgresql.org/pgsql-committers/2010-11/msg00028.php
There was a good example showing what didn't work as expected before
(along with an earlier patch that didn't everything the larger 9.1
improvement does) at
http://archives.postgresql.org/pgsql-hackers/2009-07/msg01115.php ;
"ORDER BY x DESC LIMIT 1" returns the same things as MAX(x).
It's a pretty serious issue with the partitioning in earlier versions.
I know of multiple people, myself included, who have been compelled to
apply this change to an earlier version of PostgreSQL to make larger
partitioned databases work correctly. The other option is to manually
decompose the queries into ones that target each of the child tables
individually, then combine the results, which is no fun either.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
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, jks@selectacast.net, robertmhaas@gmail.com
Subject: Re: index usage on queries on inherited tables
In-Reply-To: <4DB8CE7D.8030503@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