On 4/29/15 11:54 AM, Jonathan Vanasco wrote:
> IIRC, the best mix of performance and "product" that I've found is do something like this:
>
> SELECT * FROM (
> SELECT a,b,c FROM table_a ORDER BY TIMESTAMP DESC LIMIT 10000;
> UNION
> SELECT a,b,c FROM table_b ORDER BY TIMESTAMP DESC LIMIT 10000;
> ) as unioned
> order by unioned TIMESTAMP DESC LIMIT 100 OFFSET 0;
>
> by creating an artificial limit on the inner queries, you can save postgres from doing a lot of intensive i/o work
andmemory usage (like a lot)
> then, joining a few lists and sorting 20k (or even 100k) items is really cheap.
Only because you're using UNION. Use UNION ALL instead.
Also, you mentioned CTEs. Be aware that those are ALWAYS materialized.
Sometimes that helps performance... sometimes it hurts it horribly. I
stick with embedded subselects unless I need a specific CTE feature.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com