seqscan on UNION'ed views

[Dmitry Karasik]

Dear all,

I have a problem with seqscan I hope you might help me with.
Attached is the simple script that reproduces a database and results, which I
have tested both on 9.0.4 and 9.3-devel with identical results.

I need to have a sort of a time machine, where select statements on tables
could be easily replaced to select statements on tables as they were some time in the past,
including all related table. To do so, I used views (see in the script) that UNION
both current and archive tables and filter them by a timestamp.

The problem arises when there are two such views used in a JOIN, and apparently
the query planner doesn't look deep enough into views, creating a very slow
seqscan-based plan. The setup here demonstrates how a join that needs to
extract a single row, includes a seqscan on the whole table (see 1.Bad plan in
explain.txt, and 1000 of rows are being scanned. For the test purposes 1000
rows is not a high number, but on my system this is several millions, and that
takes significant time.

If I rewrite the query into what I would expect the planner would do for me
(see 2.Good plan), then (expectably) there are no seqscans. But I'm using an ORM
which can't rewrite joins in such a way automatically, and there are so many of
those automated queries that rewriting them by hand is also a rather bad
alternative. So my question is, is it possible to somehow nudge the planner
into the right direction?

Thank you in advance!

[Tom Lane]

Post by Dmitry Karasik
I need to have a sort of a time machine, where select statements on tables
could be easily replaced to select statements on tables as they were some time in the past,
including all related table. To do so, I used views (see in the script) that UNION
both current and archive tables and filter them by a timestamp.

If you use UNION ALL instead of UNION, you should get better results
(as well as inherently cheaper queries, since no duplicate-elimination
step will be needed).

regards, tom lane