Re: Speed difference between select ... union select ... and select from partitioned_table - Mailing list pgsql-performance

"Pablo Alcaraz" <pabloa@laotraesquina.com.ar> writes:

> 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.
>
> My question is: why? :)
>
> [pabloa@igor testeo]$ cat query-union.sql
> select e, p,  sum( c) as c
> from (
>        select e, p, count( *) as c
>        from tt_00003
>        group by e, p
>        union
>        select e, p, count( *) as c
>        from tt_00006
>        group by e, p
>        union
...


You should send along the "explain analyze" results for both queries,
otherwise we're just guessing.

Also, you should consider using UNION ALL instead of plain UNION.

Finally you should consider removing all the intermediate GROUP BYs and just
group the entire result. In theory it should be faster but in practice I'm not
sure it works out that way.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com