Materialize Subplan and push into inner index conditions

Is it possible to have the planner consider the second plan instead of the
first?

admpostgres4=> explain analyze select * from users where id in (select
user_id from user2user_group where user_group_id = 769694);

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash IN Join (cost=4.04..2302.05 rows=4 width=78) (actual
time=50.381..200.985 rows=2 loops=1)
Hash Cond: ("outer".id = "inner".user_id)
-> Append (cost=0.00..1931.68 rows=77568 width=78) (actual
time=0.004..154.629 rows=76413 loops=1)
-> Seq Scan on users (cost=0.00..1024.88 rows=44588 width=78)
(actual time=0.004..36.220 rows=43433 loops=1)
-> Seq Scan on person_user users (cost=0.00..906.80 rows=32980
width=78) (actual time=0.005..38.120 rows=32980 loops=1)
-> Hash (cost=4.04..4.04 rows=2 width=4) (actual time=0.020..0.020
rows=2 loops=1)
-> Index Scan using user2user_group_user_group_id_idx on
user2user_group (cost=0.00..4.04 rows=2 width=4) (actual time=0.011..0.014
rows=2 loops=1)
Index Cond: (user_group_id = 769694)
Total runtime: 201.070 ms
(9 rows)

admpostgres4=> select user_id from user2user_group where user_group_id =
769694;
user_id
---------
766541
766552
(2 rows)

admpostgres4=> explain analyze select * from users where id in (766541,
766552);
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=4.02..33.48 rows=9 width=78) (actual time=0.055..0.087
rows=2 loops=1)
-> Append (cost=4.02..33.48 rows=9 width=78) (actual time=0.051..0.082
rows=2 loops=1)
-> Bitmap Heap Scan on users (cost=4.02..18.10 rows=5 width=78)
(actual time=0.051..0.053 rows=2 loops=1)
Recheck Cond: ((id = 766541) OR (id = 766552))
-> BitmapOr (cost=4.02..4.02 rows=5 width=0) (actual
time=0.045..0.045 rows=0 loops=1)
-> Bitmap Index Scan on users_id_idx
(cost=0.00..2.01 rows=2 width=0) (actual time=0.034..0.034 rows=1 loops=1)
Index Cond: (id = 766541)
-> Bitmap Index Scan on users_id_idx
(cost=0.00..2.01 rows=2 width=0) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (id = 766552)
-> Bitmap Heap Scan on person_user users (cost=4.02..15.37
rows=4 width=78) (actual time=0.025..0.025 rows=0 loops=1)
Recheck Cond: ((id = 766541) OR (id = 766552))
-> BitmapOr (cost=4.02..4.02 rows=4 width=0) (actual
time=0.023..0.023 rows=0 loops=1)
-> Bitmap Index Scan on person_user_id_idx
(cost=0.00..2.01 rows=2 width=0) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: (id = 766541)
-> Bitmap Index Scan on person_user_id_idx
(cost=0.00..2.01 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: (id = 766552)
Total runtime: 0.177 ms
(17 rows)

admpostgres4=>

admpostgres4=> \d users;
Table "adm.users"
Column | Type | Modifiers
------------------+-----------------------------+---------------------
id | integer | not null
classid | integer | not null
revision | integer | not null
rev_start | timestamp without time zone |
rev_end | timestamp without time zone |
rev_timestamp | timestamp without time zone | not null
rev_state | integer | not null default 10
name | character varying |
password | character varying |
password_expires | timestamp without time zone |
password_period | integer |
Indexes:
"users_pkey" primary key, btree (revision)
"users_uidx" unique, btree (revision)
"users_id_idx" btree (id)
"users_name_idx" btree (rev_state, rev_end, name)
"users_rev_end_idx" btree (rev_end)
"users_rev_idx" btree (rev_state, rev_end)
"users_rev_start_idx" btree (rev_start)
"users_rev_state_idx" btree (rev_state)
Inherits: revision

admpostgres4=>\d person_user;
Table "adm.person_user"
Column | Type | Modifiers
------------------+-----------------------------+---------------------
id | integer | not null
classid | integer | not null
revision | integer | not null
rev_start | timestamp without time zone |
rev_end | timestamp without time zone |
rev_timestamp | timestamp without time zone | not null
rev_state | integer | not null default 10
name | character varying |
password | character varying |
password_expires | timestamp without time zone |
password_period | integer |
lastname | character varying |
description | character varying |
vat_id | character varying |
firstname | character varying |
sex | integer |
birthdate | timestamp without time zone |
title | character varying |
Indexes:
"person_user_pkey" primary key, btree (revision)
"person_user_uidx" unique, btree (revision)
"person_user_id_idx" btree (id)
"person_user_rev_end_idx" btree (rev_end)
"person_user_rev_idx" btree (rev_state, rev_end)
"person_user_rev_start_idx" btree (rev_start)
"person_user_rev_state_idx" btree (rev_state)
Inherits: users

admpostgres4=>

admpostgres4=> \d user2user_group;
Table "adm.user2user_group"
Column | Type | Modifiers
---------------+---------+-----------
user_id | integer | not null
user_group_id | integer | not null
Indexes:
"user2user_group_pkey" primary key, btree (user_id, user_group_id)
"user2user_group_uidx" unique, btree (user_id, user_group_id)
"user2user_group_user_group_id_idx" btree (user_group_id)
"user2user_group_user_id_idx" btree (user_id)

admpostgres4=>

Mit freundlichem Gruß
Jens Schicke
--
Jens Schicke j(dot)schicke(at)asco(dot)de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400