Re: Query performance problems with partitioned tables

From: Steinar H. Gunderson
Subject: Re: Query performance problems with partitioned tables
Date: ,
Msg-id: 20070430172334.GA669@uio.no
(view: Whole thread, Raw)
In response to: Re: Query performance problems with partitioned tables  (Andreas Haumer)
List: pgsql-performance

Tree view

Query performance problems with partitioned tables  (Andreas Haumer, )
 Re: Query performance problems with partitioned tables  (Guillaume Cottenceau, )
  Re: Query performance problems with partitioned tables  (Andreas Haumer, )
   Re: Query performance problems with partitioned tables  (Richard Huxton, )
    Re: Query performance problems with partitioned tables  ("Neil Peter Braggio", )
     Re: Query performance problems with partitioned tables  (Andreas Haumer, )
   Re: Query performance problems with partitioned tables  (Guillaume Cottenceau, )
    Re: Query performance problems with partitioned tables  (Gregory Stark, )
   Re: Query performance problems with partitioned tables  ("Steinar H. Gunderson", )
 Re: Query performance problems with partitioned tables  (Tom Lane, )
  Re: Query performance problems with partitioned tables  (Andreas Haumer, )
   Re: Query performance problems with partitioned tables  (Gregory Stark, )
 Re: Query performance problems with partitioned tables  (Fei Liu, )
  Re: Query performance problems with partitioned tables  ("Merlin Moncure", )
   Re: Query performance problems with partitioned tables  (Scott Marlowe, )
    Re: Query performance problems with partitioned tables  ("Merlin Moncure", )
    Re: Query performance problems with partitioned tables  (Fei Liu, )
     Re: Query performance problems with partitioned tables  (Scott Marlowe, )

On Mon, Apr 30, 2007 at 03:29:30PM +0200, Andreas Haumer wrote:
> This already gives a row matching the given WHERE clause.
> It makes no sense to scan the other tables, as the query
> asks for one row only and all the other tables have timestamps
> larger than all the timestamps in table t_mv_200601 (according
> to the CHECK constraints for the partion tables)

So for each row, it has to check all CHECK constraints to see if it has
enough rows? That sounds fairly inefficient.

I wonder if the planner could copy the limit down through the Append, though
-- it certainly doesn't need more than one row from each partition. It sounds
slightly cumbersome to try to plan such a thing, though...

/* Steinar */
--
Homepage: http://www.sesse.net/


pgsql-performance by date:

From: Gregory Stark
Date:
Subject: Re: Query performance problems with partitioned tables
From: Josh Berkus
Date:
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning