Thread: Improving Query
I have the following query that is a part of a function:
select order_details.tpv_success_id, order_details.tpv_id,
order_details.ver_code, order_details.app_id,
order_details.acct_id, order_details.first_name || ' ' ||
order_details.last_name as customer_name,
order_details.order_date as creation_date,
verification_success.checked_out, order_details.csr_name,
order_details.products, order_details.promotion,
order_details.division_id, order_details.has_dish_billing_info,
(select
array_upper(acct_product_data_requirements_details.acct_prod_ids, 1))
as num_prods,
count(distinct case when provision_issues.account_product_id is not
null and provision_issues.resolved_date is null then
provision_issues.account_product_id end ) as num_open_issues,
count(case when reconciliations.rec_id is not null and
reconciliation_cancels.rec_cancel_id is null then
reconciliations.rec_id end ) as num_provisioned,
count(case when reconciliation_cancels.rec_cancel_id is not null
then reconciliation_cancels.rec_cancel_id end ) as num_canceled
from frontier.order_details
inner join frontier.verification_success
on order_details.tpv_success_id =
verification_success.tpv_success_id
inner join frontier.acct_product_data_requirements_details
left outer join frontier.provision_issues
on provision_issues.account_product_id =
any(acct_product_data_requirements_details.acct_prod_ids) and
provision_issues.resolved_date is null
left outer join frontier.reconciliations
left outer join frontier.reconciliation_cancels
on reconciliations.rec_id =
reconciliation_cancels.rec_id
on reconciliations.account_product_id =
any(acct_product_data_requirements_details.acct_prod_ids)
on order_details.acct_id =
acct_product_data_requirements_details.acct_id
where verification_success.checked_out is null
group by order_details.tpv_success_id, order_details.tpv_id,
order_details.ver_code, order_details.app_id,
order_details.acct_id, order_details.first_name || ' ' ||
order_details.last_name, order_details.order_date,
verification_success.checked_out, order_details.csr_name,
order_details.products, order_details.promotion, num_prods,
order_details.division_id, order_details.has_dish_billing_info
having ( count(case when reconciliations.rec_id is not null and
reconciliation_cancels.rec_cancel_id is null then
reconciliations.rec_id end ) < (select
array_upper(acct_product_data_requirements_details.acct_prod_ids,
1)) ) and
(
count(distinct case when provision_issues.account_product_id is not
null and provision_issues.resolved_date is null then
provision_issues.account_product_id end ) +
count(case when reconciliations.rec_id is not null and
reconciliation_cancels.rec_cancel_id is null then
reconciliations.rec_id end ) +
count(case when reconciliation_cancels.rec_cancel_id is not null
then reconciliation_cancels.rec_cancel_id end )
) < (select
array_upper(acct_product_data_requirements_details.acct_prod_ids, 1))
and order_details.division_id =
any('{79,38,70,66,35,40,37,36,67,41,65,39}') --this array here
varies. indexes are present for the different variations
order by order_details.order_date
here is the execution plan:
Sort (cost=1350779962.18..1350969875.28 rows=75965240 width=290)
(actual time=16591.711..16591.723 rows=110 loops=1)
Sort Key: frontier.order_details.order_date
-> GroupAggregate (cost=1235567017.53..1295217399.34 rows=75965240
width=290) (actual time=16583.383..16591.420 rows=110 loops=1)
Filter: ((count(CASE WHEN ((rec_id IS NOT NULL) AND
(rec_cancel_id IS NULL)) THEN rec_id ELSE NULL::integer END) <
(subplan)) AND (((count(DISTINCT CASE WHEN ((account_product_id IS NOT
NULL) AND (resolved_date IS NULL)) THEN account_product_id ELSE
NULL::integer END) + count(CASE WHEN ((rec_id IS NOT NULL) AND
(rec_cancel_id IS NULL)) THEN rec_id ELSE NULL::integer END)) +
count(CASE WHEN (rec_cancel_id IS NOT NULL) THEN rec_cancel_id ELSE
NULL::integer END)) < (subplan)))
-> Sort (cost=1235567017.53..1238002161.29 rows=974057502
width=290) (actual time=16576.997..16577.513 rows=3366 loops=1)
Sort Key: frontier.order_details.tpv_success_id,
frontier.order_details.tpv_id, frontier.order_details.ver_code,
frontier.order_details.app_id, frontier.order_details.acct_id,
(((frontier.order_details.first_name)::text || ' '::text) ||
(frontier.order_details.last_name)::text),
frontier.order_details.order_date, verification_success.checked_out,
frontier.order_details.csr_name, frontier.order_details.products,
frontier.order_details.promotion, (subplan),
frontier.order_details.division_id,
frontier.order_details.has_dish_billing_info
-> Merge Join (cost=100001383.41..310142000.26
rows=974057502 width=290) (actual time=1055.584..16560.634 rows=3366
loops=1)
Merge Cond: ("outer".acct_id = "inner".acct_id)
-> Nested Loop Left Join
(cost=328.94..173666048.48 rows=1928122302 width=53) (actual
time=0.236..16499.771 rows=7192 loops=1)
Join Filter: ("inner".account_product_id =
ANY ("outer".acct_prod_ids))
-> Nested Loop Left Join
(cost=2.21..134714.57 rows=564852 width=45) (actual
time=0.215..1021.209 rows=5523 loops=1)
Join Filter:
("inner".account_product_id = ANY ("outer".acct_prod_ids))
-> Index Scan using "FKI_acct_id" on
acct_product_data_requirements_details (cost=0.00..488.19 rows=5484
width=33) (actual time=0.011..15.502 rows=5484 loops=1)
-> Bitmap Heap Scan on
provision_issues (cost=2.21..17.27 rows=206 width=12) (actual
time=0.035..0.106 rows=206 loops=5484)
Recheck Cond: (resolved_date IS
NULL)
-> Bitmap Index Scan on
"IX_resolved_date_null" (cost=0.00..2.21 rows=206 width=0) (actual
time=0.032..0.032 rows=206 loops=5484)
-> Materialize (cost=326.74..395.01
rows=6827 width=12) (actual time=0.000..0.852 rows=6827 loops=5523)
-> Merge Left Join
(cost=0.00..319.91 rows=6827 width=12) (actual time=0.016..13.426
rows=6827 loops=1)
Merge Cond: ("outer".rec_id =
"inner".rec_id)
-> Index Scan using
reconciliation_pkey on reconciliations (cost=0.00..215.59 rows=6827
width=8) (actual time=0.004..4.209 rows=6827 loops=1)
-> Index Scan using
"FKI_rec_id" on reconciliation_cancels (cost=0.00..56.80 rows=2436
width=8) (actual time=0.004..1.534 rows=2436 loops=1)
-> Sort (cost=100001054.46..100001061.39
rows=2770 width=241) (actual time=18.984..19.937 rows=3366 loops=1)
Sort Key: frontier.order_details.acct_id
-> Hash Join
(cost=100000131.90..100000896.08 rows=2770 width=241) (actual
time=6.525..13.644 rows=2459 loops=1)
Hash Cond: ("outer".tpv_success_id =
"inner".tpv_success_id)
-> Append
(cost=100000000.00..100000694.84 rows=2776 width=199) (actual
time=0.092..4.627 rows=2459 loops=1)
-> Seq Scan on order_details
(cost=100000000.00..100000012.45 rows=35 width=199) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: (division_id = ANY
('{79,38,70,66,35,40,37,36,67,41,65,39}'::integer[]))
-> Bitmap Heap Scan on
order_details_august_2007 order_details (cost=2.33..88.63 rows=380
width=158) (actual time=0.089..0.557 rows=330 loops=1)
Recheck Cond: (division_id
= ANY ('{79,38,70,66,35,40,37,36,67,41,65,39}'::integer[]))
-> Bitmap Index Scan on
"IX_august_2007_provision_list2" (cost=0.00..2.33 rows=380 width=0)
(actual time=0.075..0.075 rows=330 loops=1)
-> Bitmap Heap Scan on
order_details_july_2007 order_details (cost=2.31..71.39 rows=288
width=159) (actual time=0.082..0.521 rows=314 loops=1)
Recheck Cond: (division_id
= ANY ('{79,38,70,66,35,40,37,36,67,41,65,39}'::integer[]))
-> Bitmap Index Scan on
"IX_july_2007_provision_list2" (cost=0.00..2.31 rows=288 width=0)
(actual time=0.069..0.069 rows=314 loops=1)
-> Bitmap Heap Scan on
order_details_june_2007 order_details (cost=2.05..71.82 rows=279
width=148) (actual time=0.029..0.106 rows=51 loops=1)
Recheck Cond: (division_id
= ANY ('{79,38,70,66,35,40,37,36,67,41,65,39}'::integer[]))
-> Bitmap Index Scan on
"IX_june_2007_provision_list2" (cost=0.00..2.05 rows=279 width=0)
(actual time=0.022..0.022 rows=51 loops=1)
-> Bitmap Heap Scan on
order_details_october_2007 order_details (cost=7.24..279.34 rows=1060
width=159) (actual time=0.285..2.035 rows=1244 loops=1)
Recheck Cond: (division_id
= ANY ('{79,38,70,66,35,40,37,36,67,41,65,39}'::integer[]))
-> Bitmap Index Scan on
"IX_october_2007_provision_list2" (cost=0.00..7.24 rows=1060 width=0)
(actual time=0.239..0.239 rows=1244 loops=1)
-> Bitmap Heap Scan on
order_details_september_2007 order_details (cost=4.52..171.21
rows=734 width=150) (actual time=0.130..0.856 rows=520 loops=1)
Recheck Cond: (division_id
= ANY ('{79,38,70,66,35,40,37,36,67,41,65,39}'::integer[]))
-> Bitmap Index Scan on
"IX_september_2007_provision_list2" (cost=0.00..4.52 rows=734
width=0) (actual time=0.110..0.110 rows=520 loops=1)
-> Hash (cost=118.21..118.21
rows=5473 width=8) (actual time=6.414..6.414 rows=5484 loops=1)
-> Bitmap Heap Scan on
verification_success (cost=22.48..118.21 rows=5473 width=8) (actual
time=0.731..3.311 rows=5484 loops=1)
Recheck Cond: (checked_out
IS NULL)
-> Bitmap Index Scan on
"IX_checked_out_isnull" (cost=0.00..22.48 rows=5473 width=0) (actual
time=0.722..0.722 rows=5485 loops=1)
SubPlan
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.001..0.001 rows=1 loops=3366)
SubPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=780)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=2459)
Total runtime: 16593.353 ms
I have attached an erd of the tables used in this query. If it is
stripped out it can be viewed here: http://www.ketema.net/provision_list_tables_erd.jpg
My concern is with the sort step that takes 15 seconds by itself:
-> Sort (cost=1235567017.53..1238002161.29 rows=974057502 width=290)
(actual time=16576.997..16577.513 rows=3366 loops=1)
Sort Key: frontier.order_details.tpv_success_id,
frontier.order_details.tpv_id, frontier.order_details.ver_code,
frontier.order_details.app_id, frontier.order_details.acct_id,
(((frontier.order_details.first_name)::text || ' '::text) ||
(frontier.order_details.last_name)::text),
frontier.order_details.order_date, verification_success.checked_out,
frontier.order_details.csr_name, frontier.order_details.products,
frontier.order_details.promotion, (subplan),
frontier.order_details.division_id,
frontier.order_details.has_dish_billing_info
I believe this is due to the aggregate done in the select clause as
well as the sub select:
(select
array_upper(acct_product_data_requirements_details.acct_prod_ids, 1))
as num_prods,
count(distinct case when provision_issues.account_product_id is not
null and provision_issues.resolved_date is null then
provision_issues.account_product_id end ) as num_open_issues,
count(case when reconciliations.rec_id is not null and
reconciliation_cancels.rec_cancel_id is null then
reconciliations.rec_id end ) as num_provisioned,
count(case when reconciliation_cancels.rec_cancel_id is not null
then reconciliation_cancels.rec_cancel_id end ) as num_canceled
I believe the counts and sub select cause the sort to be performed
multiple times?
How can I improve this step?
Things I have thought about:
1)Creating indexes on the aggregates...Found out this can't be done.
2)Create Views of the counts and the sub select...is this any faster
as the view is executed at run time anyway?
3)Create actual tables of the sub select and aggregates...How would
this be maintained to ensure it was always accurate?
4)Increasing hardware resources. Currently box is on a single
processor amd64 with 8Gb of RAM. below are the settings for resource
usage.
shared_buffers = 65536
temp_buffers = 5000
max_prepared_transactions = 2000
work_mem = 131072
maintenance_work_mem = 512000
max_stack_depth = 7168
max_fsm_pages = 160000
max_fsm_relations = 4000
The only function of this box if for Pg, so I do not mind it using
every last drop of ram and resources that it can.
5)Upgrade version of pg..currently is running 8.1.4
Would appreciate any suggestions.
Thanks
http://pgsql.privatepaste.com/7ffDdPQvIN
Ketema wrote: > I have the following query that is a part of a function: Yikes! Difficult to get a clear view of what this query is doing. OK, I'm assuming you're vacuumed and analysed on all these tables... > My concern is with the sort step that takes 15 seconds by itself: > > -> Sort (cost=1235567017.53..1238002161.29 rows=974057502 width=290) > (actual time=16576.997..16577.513 rows=3366 loops=1) That's taking hardly any time, the startup time is 16576.997 already. Of course, the row estimate is *way* out of line. If you look here (where the explain is a bit easier to see) http://explain-analyze.info/query_plans/1258-query-plan-224 The two main things to look at seem to be the nested loops near the top and a few lines down the materialise (cost=326... These two nested loops seem to be pushing the row estimates wildly out of reality. They also consume much of the time. The immediate thing that leaps out here is that you are trying to join an int to an array of ints. Why are you using this setup rather than a separate table? > How can I improve this step? > > Things I have thought about: > 1)Creating indexes on the aggregates...Found out this can't be done. Nope - not sure what it would mean in any case. > 2)Create Views of the counts and the sub select...is this any faster > as the view is executed at run time anyway? Might make the query easier to write, won't make it faster. Not without materialised views which are the fancy name for #3... > 3)Create actual tables of the sub select and aggregates...How would > this be maintained to ensure it was always accurate? Triggers. > 4)Increasing hardware resources. Currently box is on a single > processor amd64 with 8Gb of RAM. below are the settings for resource > usage. > shared_buffers = 65536 > temp_buffers = 5000 > max_prepared_transactions = 2000 ???? > work_mem = 131072 > maintenance_work_mem = 512000 Can't say about these without knowing whether you've got only one connection or 100. > max_stack_depth = 7168 > max_fsm_pages = 160000 > max_fsm_relations = 4000 > The only function of this box if for Pg, so I do not mind it using > every last drop of ram and resources that it can. > 5)Upgrade version of pg..currently is running 8.1.4 Well every version gets better at planning, so it can't hurt. -- Richard Huxton Archonet Ltd
On Oct 30, 2007, at 7:18 , Ketema wrote: > here is the execution plan: I've put this online here: http://explain-analyze.info/query_plans/1259-ketema-2007-10-30 > I have attached an erd of the tables used in this query. If it is > stripped out it can be viewed here: http://www.ketema.net/ > provision_list_tables_erd.jpg > > My concern is with the sort step that takes 15 seconds by itself: > > -> Sort (cost=1235567017.53..1238002161.29 rows=974057502 width=290) > (actual time=16576.997..16577.513 rows=3366 loops=1) What jumps out at me is the huge difference in estimated and returned rows, and the huge cost estimates. Have you analyzed recently? Do you have enable_seqscan disabled? It appears so, due to the high cost here: -> Seq Scan on order_details (cost=100000000.0..100000012.45 rows=35 width=199) (actual time=0.001..0.001 rows=0 loops=1) http://explain-analyze.info/query_plans/1259-ketema-2007-10-30#node-3594 What does it look like with seqscan enabled? > 2)Create Views of the counts and the sub select...is this any faster > as the view is executed at run time anyway? Views aren't materialized: it's like inlining the definition of the view itself in the query. > 3)Create actual tables of the sub select and aggregates...How would > this be maintained to ensure it was always accurate? One way would be to update the summaries using triggers. Hopefully you won't need to do this after analyzing and perhaps tweaking your server configuration. Unfortunately I don't have the time to look at the query plan in more detail, but I suspect there's a better way to get the results you're looking for. Michael Glaesemann grzm seespotcode net
On Oct 30, 2007, at 9:23 AM, Richard Huxton wrote: > Ketema wrote: >> I have the following query that is a part of a function: > > Yikes! Difficult to get a clear view of what this query is doing. It seems complicated because you only have a small subset of the schema these tables tie into. Be happy to share the whole thing, if it is needed. > > OK, I'm assuming you're vacuumed and analysed on all these tables... Yes. Auto-vacuum is on and do a Full vacuuum every 2 days. > > >> My concern is with the sort step that takes 15 seconds by itself: >> -> Sort (cost=1235567017.53..1238002161.29 rows=974057502 >> width=290) >> (actual time=16576.997..16577.513 rows=3366 loops=1) > > That's taking hardly any time, the startup time is 16576.997 > already. Of course, the row estimate is *way* out of line. OK. I misread the plan and took start up time as the time it took to perform operation. Thanks for the link to explain analyze. > > If you look here (where the explain is a bit easier to see) > http://explain-analyze.info/query_plans/1258-query-plan-224 > > The two main things to look at seem to be the nested loops near the > top and a few lines down the materialise (cost=326... > > These two nested loops seem to be pushing the row estimates wildly > out of reality. They also consume much of the time. > > The immediate thing that leaps out here is that you are trying to > join an int to an array of ints. Why are you using this setup > rather than a separate table? I see what you are talking about. When I initially used this set up it was because I wanted to avoid a table that had a ton of rows in it that I knew I would have to join to often. So I made a column that holds on average 4 or 5 ints representing "products" on a particular "order". I did not realize that using a function in the join would be worse that simply having a large table. > >> How can I improve this step? >> Things I have thought about: >> 1)Creating indexes on the aggregates...Found out this can't be done. > > Nope - not sure what it would mean in any case. My initial thought was the counts were causing the slow up. THis is not the issue though as you have shown. > >> 2)Create Views of the counts and the sub select...is this any faster >> as the view is executed at run time anyway? > > Might make the query easier to write, won't make it faster. Not > without materialised views which are the fancy name for #3... > >> 3)Create actual tables of the sub select and aggregates...How would >> this be maintained to ensure it was always accurate? > > Triggers. Because of the use of this system I may take this route as I think it will be less changes. > >> 4)Increasing hardware resources. Currently box is on a single >> processor amd64 with 8Gb of RAM. below are the settings for resource >> usage. >> shared_buffers = 65536 >> temp_buffers = 5000 >> max_prepared_transactions = 2000 > > ???? These are settings out of postgresql.conf Currently systctl.conf is set to kernel.shmmax = 805306368 connections are at 300 and I usually have about 200 connections open. > >> work_mem = 131072 >> maintenance_work_mem = 512000 > > Can't say about these without knowing whether you've got only one > connection or 100. > >> max_stack_depth = 7168 >> max_fsm_pages = 160000 >> max_fsm_relations = 4000 >> The only function of this box if for Pg, so I do not mind it using >> every last drop of ram and resources that it can. >> 5)Upgrade version of pg..currently is running 8.1.4 > > Well every version gets better at planning, so it can't hurt. At one point I did go to 8.2.3 on a dev box and performance was horrible. Have not had opportunity to see how to make postgresql.conf file in 8.2 match settings in 8.1 as some things have changed. > > -- > Richard Huxton > Archonet Ltd
On Oct 30, 2007, at 9:31 AM, Michael Glaesemann wrote: > > On Oct 30, 2007, at 7:18 , Ketema wrote: > >> here is the execution plan: > > I've put this online here: > > http://explain-analyze.info/query_plans/1259-ketema-2007-10-30 > >> I have attached an erd of the tables used in this query. If it is >> stripped out it can be viewed here: http://www.ketema.net/ >> provision_list_tables_erd.jpg >> >> My concern is with the sort step that takes 15 seconds by itself: >> >> -> Sort (cost=1235567017.53..1238002161.29 rows=974057502 >> width=290) >> (actual time=16576.997..16577.513 rows=3366 loops=1) > > What jumps out at me is the huge difference in estimated and > returned rows, and the huge cost estimates. Have you analyzed > recently? Yes. I run vacuum FULL ANALYZE VERBOSE every two days with a cron job. I am running again now any way. > > Do you have enable_seqscan disabled? It appears so, due to the high > cost here: > > -> Seq Scan on order_details (cost=100000000.0..100000012.45 > rows=35 width=199) (actual time=0.001..0.001 rows=0 loops=1) > > http://explain-analyze.info/query_plans/1259- > ketema-2007-10-30#node-3594 > > What does it look like with seqscan enabled? it was disabled. new plan posted here: http://explain-analyze.info/query_plans/1261-provision-list-seq-scan- enabled > > >> 2)Create Views of the counts and the sub select...is this any faster >> as the view is executed at run time anyway? > > Views aren't materialized: it's like inlining the definition of the > view itself in the query. > >> 3)Create actual tables of the sub select and aggregates...How would >> this be maintained to ensure it was always accurate? > > One way would be to update the summaries using triggers. Hopefully > you won't need to do this after analyzing and perhaps tweaking your > server configuration. > > Unfortunately I don't have the time to look at the query plan in > more detail, but I suspect there's a better way to get the results > you're looking for. > > Michael Glaesemann > grzm seespotcode net > >
Richard Huxton <dev@archonet.com> writes:
> Ketema wrote:
>> 5)Upgrade version of pg..currently is running 8.1.4
> Well every version gets better at planning, so it can't hurt.
+1 ... there are at least two things about this query that 8.2 could be
expected to be a great deal smarter about:
* mixed outer and inner joins
* something = ANY(array)
regards, tom lane
I am definitely interested in upgrading. Is there a guide out there that perhaps was created to explain the changes in the config files from 8.1 to 8.2 ? Migration guide I guess? On Oct 30, 2007, at 11:39 AM, Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: >> Ketema wrote: >>> 5)Upgrade version of pg..currently is running 8.1.4 > >> Well every version gets better at planning, so it can't hurt. > > +1 ... there are at least two things about this query that 8.2 > could be > expected to be a great deal smarter about: > * mixed outer and inner joins > * something = ANY(array) > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend