pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMaterialize Subplan and push into inner index conditions
2+ messages / 2 participants
[nested] [flat]
* Materialize Subplan and push into inner index conditions
@ 2006-01-03 15:08 Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de>
2006-01-03 15:43 ` Re: Materialize Subplan and push into inner index conditions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Jens-Wolfhard Schicke @ 2006-01-03 15:08 UTC (permalink / raw)
To: pgsql-performance
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.schicke@asco.de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Materialize Subplan and push into inner index conditions
2006-01-03 15:08 Materialize Subplan and push into inner index conditions Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de>
@ 2006-01-03 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-01-03 15:43 UTC (permalink / raw)
To: Jens-Wolfhard Schicke <j.schicke@asco.de>; +Cc: pgsql-performance
Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de> writes:
> Is it possible to have the planner consider the second plan instead of the
> first?
At the moment, only if you get rid of the inheritance. The planner's
not very smart at all when faced with joining inheritance trees.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-03 15:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-03 15:08 Materialize Subplan and push into inner index conditions Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de>
2006-01-03 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox