From: Svenne Krap <svenne@krap.dk>
To: pgsql-performance@postgresql.org
Subject: Performance problems with multiple layers of functions
Date: Fri, 24 Mar 2006 13:49:17 +0100
Message-ID: <4423EACD.80706@krap.dk> (raw)
Hi there.
I have hit a edge in the planning and I hope you can help.
The system uses a lot of stored procedures to move as much of the
intelligence into the database layer as possible.
My (development) query looks like and runs reasonably fast:
explain analyze select dataset_id, entity, sum(amount) from
entrydata_current where flow_direction in (select * from
outflow_direction(dataset_id)) and dataset_id in (122,112,125,89,111)
group by dataset_id, entity;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=918171.00..918171.30 rows=24 width=19) (actual
time=11533.297..11533.340 rows=50 loops=1)
-> Bitmap Heap Scan on entrydata_current (cost=676.72..917736.04
rows=57994 width=19) (actual time=23.921..11425.373 rows=37870 loops=1)
Recheck Cond: ((dataset_id = 122) OR (dataset_id = 112) OR
(dataset_id = 125) OR (dataset_id = 89) OR (dataset_id = 111))
Filter: (subplan)
-> BitmapOr (cost=676.72..676.72 rows=117633 width=0) (actual
time=15.765..15.765 rows=0 loops=1)
-> Bitmap Index Scan on entrydata_current_dataset_idx
(cost=0.00..83.97 rows=14563 width=0) (actual time=1.881..1.881
rows=13728 loops=1)
Index Cond: (dataset_id = 122)
-> Bitmap Index Scan on entrydata_current_dataset_idx
(cost=0.00..156.12 rows=27176 width=0) (actual time=3.508..3.508
rows=25748 loops=1)
Index Cond: (dataset_id = 112)
-> Bitmap Index Scan on entrydata_current_dataset_idx
(cost=0.00..124.24 rows=21498 width=0) (actual time=2.729..2.729
rows=20114 loops=1)
Index Cond: (dataset_id = 125)
-> Bitmap Index Scan on entrydata_current_dataset_idx
(cost=0.00..102.20 rows=17771 width=0) (actual time=2.351..2.351
rows=17344 loops=1)
Index Cond: (dataset_id = 89)
-> Bitmap Index Scan on entrydata_current_dataset_idx
(cost=0.00..210.19 rows=36625 width=0) (actual time=5.292..5.292
rows=37118 loops=1)
Index Cond: (dataset_id = 111)
SubPlan
-> Function Scan on outflow_direction (cost=0.00..12.50
rows=1000 width=4) (actual time=0.093..0.095 rows=4 loops=114052)
Total runtime: 11540.506 ms
(18 rows)
The problem is, that the application should not need to know the five
dataset_ids (it will always know one - its own). So I make a function to
return the five ids and then the query looks like:
explain select dataset_id, entity, sum(amount) from entrydata_current
where flow_direction in (select * from outflow_direction(dataset_id))
and dataset_id in (select * from get_dataset_ids(122)) group by
dataset_id, entity;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=24672195.68..24672203.88 rows=656 width=19)
-> Hash IN Join (cost=15.00..24660005.45 rows=1625364 width=19)
Hash Cond: ("outer".dataset_id = "inner".get_dataset_ids)
-> Index Scan using entrydata_current_dataset_idx on
entrydata_current (cost=0.00..24558405.20 rows=1625364 width=19)
Filter: (subplan)
SubPlan
-> Function Scan on outflow_direction
(cost=0.00..12.50 rows=1000 width=4)
-> Hash (cost=12.50..12.50 rows=1000 width=4)
-> Function Scan on get_dataset_ids (cost=0.00..12.50
rows=1000 width=4)
(9 rows)
which does not return within 10 minutes - which is unacceptable.
Is there any way to get a better plan for the second ? The planner
should really see the two queries as equal as there is no dependencies
between the outer query and get_dataset_ids (isn't it called constant
folding?).
Thanks in advance
Svenne
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: svenne@krap.dk
Subject: Re: Performance problems with multiple layers of functions
In-Reply-To: <4423EACD.80706@krap.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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