Thread: Pushing IN (subquery) down through UNION ALL?

I'm using PostgreSQL 8.3.3 and I have a view that does a UNION ALL on two joins and it doesn't seem to want to push the IN (subquery) optimization down into the plan for the two queries being unioned. Is there something I can do to fix this? Or is it just a limitation of the planner/optimizer?

I also tried this with 8.4.7 and it seemed to exhibit the same behaviour, so here's an example of what I'm talking about (obviously in a real system I'd have indexes and all that other fun stuff):

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE addresses1 (userid INTEGER, value INTEGER);
CREATE TABLE addresses1 (userid INTEGER, value INTEGER);
CREATE VIEW addressesall AS SELECT u.id, u.name, a.value FROM addresses1 AS a JOIN users AS u ON a.userid=u.id UNION ALL SELECT u.id, u.name, a.value FROM addresses2 AS a JOIN users AS u ON a.userid=u.id;


Here's the EXPLAIN output for two example queries:

test=# EXPLAIN ANALYZE SELECT * FROM addressesall WHERE id IN (SELECT id FROM users WHERE name='A');
                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------

  Hash Semi Join  (cost=2.15..5.58 rows=1 width=40) (actual time=0.144..0.340 rows=3 loops=1)
  Hash Cond: (u.id = users.id)
  ->  Append  (cost=1.09..4.48 rows=9 width=40) (actual time=0.059..0.239 rows=9 loops=1)
        ->  Hash Join  (cost=1.09..2.19 rows=4 width=10) (actual time=0.055..0.075 rows=4 loops=1)
              Hash Cond: (a.userid = u.id)
              ->  Seq Scan on addresses1 a  (cost=0.00..1.04 rows=4 width=8) (actual time=0.006..0.013 rows=4 loops=1)
              ->  Hash  (cost=1.04..1.04 rows=4 width=6) (actual time=0.019..0.019 rows=4 loops=1)
                    ->  Seq Scan on users u  (cost=0.00..1.04 rows=4 width=6) (actual time=0.003..0.008 rows=4 loops=1)
        ->  Hash Join  (cost=1.09..2.21 rows=5 width=10) (actual time=0.109..0.133 rows=5 loops=1)
              Hash Cond: (a.userid = u.id)
              ->  Seq Scan on addresses2 a  (cost=0.00..1.05 rows=5 width=8) (actual time=0.004..0.012 rows=5 loops=1)
              ->  Hash  (cost=1.04..1.04 rows=4 width=6) (actual time=0.020..0.020 rows=4 loops=1)
                    ->  Seq Scan on users u  (cost=0.00..1.04 rows=4 width=6) (actual time=0.004..0.010 rows=4 loops=1)
  ->  Hash  (cost=1.05..1.05 rows=1 width=4) (actual time=0.053..0.053 rows=1 loops=1)
        ->  Seq Scan on users  (cost=0.00..1.05 rows=1 width=4) (actual time=0.032..0.040 rows=1 loops=1)
              Filter: (name = 'A'::text)
 Total runtime: 0.519 ms
(17 rows)

test=# EXPLAIN ANALYZE SELECT * FROM addressesall WHERE id IN (1);
                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..4.27 rows=3 width=40) (actual time=0.053..0.114 rows=3 loops=1)
  ->  Append  (cost=0.00..4.27 rows=3 width=40) (actual time=0.049..0.101 rows=3 loops=1)
        ->  Nested Loop  (cost=0.00..2.12 rows=2 width=10) (actual time=0.046..0.063 rows=2 loops=1)
              ->  Seq Scan on users u  (cost=0.00..1.05 rows=1 width=6) (actual time=0.025..0.028 rows=1 loops=1)
                    Filter: (id = 1)
              ->  Seq Scan on addresses1 a  (cost=0.00..1.05 rows=2 width=8) (actual time=0.009..0.017 rows=2 loops=1)
                    Filter: (a.userid = 1)
        ->  Nested Loop  (cost=0.00..2.12 rows=1 width=10) (actual time=0.015..0.025 rows=1 loops=1)
              ->  Seq Scan on addresses2 a  (cost=0.00..1.06 rows=1 width=8) (actual time=0.005..0.008 rows=1 loops=1)
                    Filter: (userid = 1)
              ->  Seq Scan on users u  (cost=0.00..1.05 rows=1 width=6) (actual time=0.004..0.007 rows=1 loops=1)
                    Filter: (u.id = 1)
 Total runtime: 0.251 ms
(13 rows)

You'll notice that the subquery version is doing the full join and then the filtering, but the explicitly listed version pushing the filtering into the plan before the join. Is there a way to make the subquery version perform the same optimization?

Thanks,
Dave

You'll notice that the subquery version is doing the full join and then the filtering, but the explicitly listed version pushing the filtering into the plan before the join. Is there a way to make the subquery version perform the same optimization?

On Thu, Feb 24, 2011 at 16:14, Dave Johansen <davejohansen@gmail.com> wrote:

You'll notice that the subquery version is doing the full join and then the filtering, but the explicitly listed version pushing the filtering into the plan before the join. Is there a way to make the subquery version perform the same optimization?

EXPLAIN ANALYZE SELECT * FROM addressesall WHERE id = ANY (array(SELECT id FROM users WHERE name='A'));

(Tested on 9.0.3)

On Thu, Feb 24, 2011 at 12:33 PM, Vik Reykja <vikreykja@gmail.com> wrote:

On Thu, Feb 24, 2011 at 16:14, Dave Johansen <davejohansen@gmail.com> wrote:

You'll notice that the subquery version is doing the full join and then the filtering, but the explicitly listed version pushing the filtering into the plan before the join. Is there a way to make the subquery version perform the same optimization?

EXPLAIN ANALYZE SELECT * FROM addressesall WHERE id = ANY (array(SELECT id FROM users WHERE name='A'));

(Tested on 9.0.3)

I just tested that on 8.3.3 and it performed quickly like I expected the other query to, so that did the trick.

Thanks a ton,

On Thu, Feb 24, 2011 at 20:56, Dave Johansen <davejohansen@gmail.com> wrote:

On Thu, Feb 24, 2011 at 12:33 PM, Vik Reykja <vikreykja@gmail.com> wrote:

On Thu, Feb 24, 2011 at 16:14, Dave Johansen <davejohansen@gmail.com> wrote:

You'll notice that the subquery version is doing the full join and then the filtering, but the explicitly listed version pushing the filtering into the plan before the join. Is there a way to make the subquery version perform the same optimization?

EXPLAIN ANALYZE SELECT * FROM addressesall WHERE id = ANY (array(SELECT id FROM users WHERE name='A'));

(Tested on 9.0.3)

I just tested that on 8.3.3 and it performed quickly like I expected the other query to, so that did the trick.

Is there any good reason you're not using 8.3.14?