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

Re: Speed difference between select ... union select ...and select from partitioned_table

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Pablo Alcaraz <pabloa(at)laotraesquina(dot)com(dot)ar>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Speed difference between select ... union select ...and select from partitioned_table
Date: 2007-10-27 21:26:49
Message-ID: 1193520409.4242.604.camel@ebony.site (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 2007-10-26 at 16:37 -0400, Pablo Alcaraz wrote:

> I executed 2 equivalents queries. The first one uses a union structure. 
> The second uses a partitioned table. The tables are the same with 30 
> millions of rows each one and the returned rows are the same.
> 
> But the union query perform faster than the partitioned query.
> 
> My question is: why? :)

The two queries are equivalent but they have different execution plans.

The UNION query has explicit GROUP BY operations within it. We do not
currently perform a push-down operation onto the individual partitions.
This results in more data copying as well as requiring a single very
large sort, rather than lots of small ones. That is probably enough to
allow it to perform the sort in memory rather than on-disk, thus
allowing a considerable speed-up.

This is on my list of requirements for further partitioning improvements
in 8.4 or beyond.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


In response to

pgsql-performance by date

Next:From: Simon RiggsDate: 2007-10-27 21:31:22
Subject: Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1
Previous:From: Luke LonerganDate: 2007-10-27 19:28:04
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

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