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

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

From: Jeff Davis <pgsql(at)j-davis(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-26 21:07:01
Message-ID: 1193432821.7624.83.camel@dogma.ljc.laika.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 2007-10-26 at 16:37 -0400, Pablo Alcaraz wrote:
> Hi List!
> 
> 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.
> 

I think you mean to use UNION ALL here. UNION forces a DISTINCT, which
results in a sort operation. What surprises me is that the UNION is
actually faster than the partitioning using inheritance.

I suspect it has something to do with the GROUP BYs, but we won't know
until you post EXPLAIN ANALYZE results.

    Regards,
	Jeff Davis


In response to

Responses

pgsql-performance by date

Next:From: Gregory StarkDate: 2007-10-26 21:09:20
Subject: Re: Suggestions on an update query
Previous:From: Pablo AlcarazDate: 2007-10-26 20:37:40
Subject: Speed difference between select ... union select ... and select from partitioned_table

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