StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSlow query on "UNION ALL" view
text
Body
copied!<p>I have a DB view which basically consists of two <code>SELECT</code> queries with <code>UNION ALL</code>, like this:</p> <pre><code>CREATE VIEW v AS SELECT time, etc. FROM t1 // #1... UNION ALL SELECT time, etc. FROM t2 // #2... </code></pre> <p>The problem is that selects of the form</p> <pre><code>SELECT ... FROM v WHERE time >= ... AND time < ... </code></pre> <p>perform really really slow on it.</p> <p>Both SELECT #1 and #2 are decently fast, properly indexed and so on: when I create views v1 and v2 like:</p> <pre><code>CREATE VIEW v1 AS SELECT time, etc. FROM t1 // #1... CREATE VIEW v2 AS SELECT time, etc. FROM t2 // #2... </code></pre> <p>And the same SELECT, with same WHERE condition as the above works OK on them individually.</p> <p>Any ideas about where might be the problem and how to solve it?</p> <p>(Just to mention, it's one of the recent Postgres versions.)</p> <p>Edit: Adding anonymized query plans (thaks to @filiprem for the link to an awesome tool):</p> <p>v1:</p> <pre><code>Aggregate (cost=9825.510..9825.520 rows=1 width=53) (actual time=59.995..59.995 rows=1 loops=1) -> Index Scan using delta on echo alpha (cost=0.000..9815.880 rows=3850 width=53) (actual time=0.039..53.418 rows=33122 loops=1) Index Cond: (("juliet" >= 'seven'::uniform bravo_victor oscar whiskey) AND ("juliet" <= 'november'::uniform bravo_victor oscar whiskey)) Filter: ((NOT victor) AND ((bravo_sierra five NULL) OR ((bravo_sierra)::golf <> 'india'::golf))) </code></pre> <p>v2:</p> <pre><code>Aggregate (cost=15.470..15.480 rows=1 width=33) (actual time=0.231..0.231 rows=1 loops=1) -> Index Scan using yankee on six charlie (cost=0.000..15.220 rows=99 width=33) (actual time=0.035..0.186 rows=140 loops=1) Index Cond: (("juliet" >= 'seven'::uniform bravo oscar whiskey) AND ("juliet" <= 'november'::uniform bravo oscar whiskey)) Filter: (NOT victor) </code></pre> <p>v:</p> <pre><code>Aggregate (cost=47181.850..47181.860 rows=1 width=0) (actual time=37317.291..37317.291 rows=1 loops=1) -> Append (cost=42.170..47132.480 rows=3949 width=97) (actual time=1.277..37304.453 rows=33262 loops=1) -> Nested Loop Left Join (cost=42.170..47052.250 rows=3850 width=99) (actual time=1.275..37288.465 rows=33122 loops=1) -> Hash Left Join (cost=42.170..9910.990 rows=3850 width=115) (actual time=1.123..117.797 rows=33122 loops=1) Hash Cond: ((alpha_seven.two)::golf = (quebec_three.two)::golf) -> Index Scan using delta on echo alpha_seven (cost=0.000..9815.880 rows=3850 width=132) (actual time=0.038..77.866 rows=33122 loops=1) Index Cond: (("juliet" >= 'seven'::uniform bravo_victor oscar whiskey_two) AND ("juliet" <= 'november'::uniform bravo_victor oscar whiskey_two)) Filter: ((NOT victor) AND ((bravo_sierra five NULL) OR ((bravo_sierra)::golf <> 'india'::golf))) -> Hash (cost=30.410..30.410 rows=941 width=49) (actual time=1.068..1.068 rows=941 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 75kB -> Seq Scan on alpha_india quebec_three (cost=0.000..30.410 rows=941 width=49) (actual time=0.010..0.486 rows=941 loops=1) -> Index Scan using mike on hotel quebec_sierra (cost=0.000..9.630 rows=1 width=24) (actual time=1.112..1.119 rows=1 loops=33122) Index Cond: ((alpha_seven.zulu)::golf = (quebec_sierra.zulu)::golf) -> Subquery Scan on "*SELECT* 2" (cost=34.080..41.730 rows=99 width=38) (actual time=1.081..1.951 rows=140 loops=1) -> Merge Right Join (cost=34.080..40.740 rows=99 width=38) (actual time=1.080..1.872 rows=140 loops=1) Merge Cond: ((quebec_three.two)::golf = (charlie.two)::golf) -> Index Scan using whiskey_golf on alpha_india quebec_three (cost=0.000..174.220 rows=941 width=49) (actual time=0.017..0.122 rows=105 loops=1) -> Sort (cost=18.500..18.750 rows=99 width=55) (actual time=0.915..0.952 rows=140 loops=1) Sort Key: charlie.two Sort Method: quicksort Memory: 44kB -> Index Scan using yankee on six charlie (cost=0.000..15.220 rows=99 width=55) (actual time=0.022..0.175 rows=140 loops=1) Index Cond: (("juliet" >= 'seven'::uniform bravo_victor oscar whiskey_two) AND ("juliet" <= 'november'::uniform bravo_victor oscar whiskey_two)) Filter: (NOT victor) </code></pre> <p><code>juliet</code> is <code>time</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload