Message-ID: <4DC7D3F3.9010002@2ndQuadrant.com>
Date: Mon, 09 May 2011 07:45:55 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110307 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: indexes ignored when querying the master table
References: <4DC46470.20004@refusion.com>
In-Reply-To: <4DC46470.20004@refusion.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 05/06/2011 05:13 PM, Thomas Hägi wrote:
> the query "SELECT * FROM data.logs ORDER BY re_timestamp DESC LIMIT 
> 100" does use seq scans on all tables instead of using the existing 
> indexes which takes ages. when issuing the the same query to one of 
> the child tables directly ("SELECT * FROM data.logs_2011 ORDER BY 
> re_timestamp DESC LIMIT 100") the index is used as expected and the 
> data returned quickly.
>

Let's see, cut and paste 
http://archives.postgresql.org/message-id/4DB8CE7D.8030503@2ndquadrant.com 
and:

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.

(Am thinking about a documentation backpatch pointing out this limitation)


-- 
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