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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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