On Thu, Sep 22, 2016 at 11:35 PM, Sven R. Kunze <srkunze@xxxxxxx> wrote:
Thanks a lot Madusudanan, Igor, Lutz and Jeff for your suggestions.
What I can confirm is that the UNION ideas runs extremely fast (don't have access to the db right now to test the subquery idea, but will check next week as I travel right now). Thanks again! :)
I was wondering: would it be possible for PostgreSQL to rewrite the query to generate the UNION (or subquery plan if it's also fast) on it's own?
I don't know what the subquery plan is, I don't see references to that in the email chain.
I don't believe that current versions of PostgreSQL are capable of rewriting the plan in the style of a union. It is not just a matter of tweaking the cost estimates, it simply never considers such a plan in the first place given the text of your query.
Perhaps some future version of PostgreSQL could do so, but my gut feeling is that that is not very likely. It would take a lot of work, would risk breaking or slowing down other things, and is probably too much of a niche issue to attract a lot of interest.
Why not just use the union? Are you using a framework which generates the query automatically and you have no control over it? Or do you just think it is ugly or fragile for some other reason?
Perhaps moving the union from the outside to the inside would be more suitable? That way teh select list is only specified once, and if you AND more clauses into the WHERE condition they also only need to be specified once.
SELECT * FROM big_table
WHERE
id in (SELECT big_table_id FROM table_a WHERE "table_a"."item_id" IN (<handful of items>) union
WHERE
id in (SELECT big_table_id FROM table_a WHERE "table_a"."item_id" IN (<handful of items>) union
SELECT big_table_id FROM table_a WHERE "table_b"."item_id" IN (<handful of items>)
);
Cheers,
Jeff
