Hi.
I'm searching in a medium-sized table (135k rows, 29 columns). Some of the
records point to other (parent) records, whose data values have to be used
for filtering as well as for joins, instead of the record's own fields.
Grouping the different types of records into "subset" views, the query looks
like this:
SELECT ... FROM subset1 JOIN (tables...) WHERE (filters...)
UNION ALL
SELECT ... FROM subset2 JOIN (tables...) WHERE (filters...)
UNION ALL
SELECT ... FROM subset3 JOIN (tables...) WHERE (filters...)
"(tables...)" and "(filters...)" are exactly the same for all selects. Since
the final form of this query may have up to 20 WHERE filters and 14 joins to
other tables, I tried to eliminate this duplication and apply the filters
and joins only to the result of the unfiltered union:
SELECT combined.*
FROM (
SELECT * FROM subset1
UNION ALL
SELECT * FROM subset2
UNION ALL
SELECT * FROM subset3
) combined
JOIN (tables...)
WHERE (filters...)
This gives the same result, but increases the execution time from 9ms to
500ms in the simplest case (1 join, 1 filter). The views are not the cause
of the slowdown; I've also tried this with the view SQL inlined. The main
reason seems to be that the second example needs three seq scans of the
underlying table, while the fast query can make use of indexes (from the
join, I assume).
Is there a way to have the performance of the first query example while
avoiding the duplication of filters and joins?
(I had originally posted a similar question on dba.stackexchange.com [1].
That question has more details, including query plans, but I realize now
that it probably wasn't a good fit for that site.)
Thanks in advance,
Stefan
[1] http://dba.stackexchange.com/questions/136653/
