PostgreSQL Performance - Re: Slow query. Any way to speed up?

Author

Re: Slow query. Any way to speed up?

Tom Lane

2006-01-06, 3:24 am

Patrick Hatcher <PHatcher@macys.com> writes:
> The following SQL takes 4+ mins to run. I have indexes on all join fields
> and I've tried rearranging the table orders but haven't had any luck.

Please show EXPLAIN ANALYZE output, not just EXPLAIN. It's impossible
to tell whether the planner is making any wrong guesses when you can't
see the actual times/rowcounts ...

(BTW, 7.4 is looking pretty long in the tooth.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Patrick Hatcher

2006-01-06, 1:24 pm

Duh sorry. We will eventually move to 8.x, it's just a matter of finding
the time:

Explain analyze
Select gmmid, gmmname, divid, divname, feddept, fedvend,itemnumber as
mstyle,amc_week_id,
sum(tran_itm_total) as net_dollars

FROM
public. tbldetaillevel_repor
t a2 join cdm.cdm_ddw_tran_item a1 on
a1.item_upc = a2.upc
join public.date_dim a3 on a3.date_dim_id = a1.cal_date
where
a3.date_dim_id between '2005-10-30' and '2005-12-31'
and
a1.appl_id in ('MCOM','NET')
and
a1.tran_typ_id in ('S','R')
group by 1,2,3,4,5,6,7,8
order by 1,2,3,4,5,6,7,8

GroupAggregate (cost=1648783.47..1650793.74 rows=73101 width=65) (actual
time=744556.289..753136.278 rows=168343 loops=1)
-> Sort (cost=1648783.47..1648966.22 rows=73101 width=65) (actual
time=744556.236..746634.566 rows=1185096 loops=1)
Sort Key: a2.gmmid, a2.gmmname, a2.divid, a2.divname, a2.feddept,
a2.fedvend, a2.itemnumber, a3.amc_week_id
-> Merge Join (cost=1598067.59..1642877.78 rows=73101 width=65)
(actual time=564862.772..636550.484 rows=1185096 loops=1)
Merge Cond: ("outer".upc = "inner".item_upc)
-> Index Scan using report_upc_idx on tbldetaillevel_repor
t
a2 (cost=0.00..47642.36 rows=367309 width=58) (actual
time=82.512..65458.137 rows=365989 loops=1)
-> Sort (cost=1598067.59..1598250.34 rows=73100 width=23)
(actual time=564764.506..566529.796 rows=1248862 loops=1)
Sort Key: a1.item_upc
-> Hash Join (cost=94.25..1592161.99 rows=73100
width=23) (actual time=493500.913..548924.039 rows=1248851 loops=1)
Hash Cond: ("outer".cal_date =
"inner".date_dim_id)
-> Seq Scan on cdm_ddw_tran_item a1
(cost=0.00..1547562.88 rows=8754773 width=23) (actual
time=14.219..535704.691 rows=10838135 loops=1)
Filter: ((((appl_id)::text = 'MCOM'::text)
OR ((appl_id)::text = 'NET'::text)) AND ((tran_typ_id = 'S'::bpchar) OR
(tran_typ_id = 'R'::bpchar)))
-> Hash (cost=94.09..94.09 rows=64 width=8)
(actual time=362.953..362.953 rows=0 loops=1)
-> Index Scan using date_date_idx on
date_dim a3 (cost=0.00..94.09 rows=64 width=8) (actual
time=93.710..362.802 rows=63 loops=1)
Index Cond: ((date_dim_id >=
'2005-10-30'::date) AND (date_dim_id <= '2005-12-31'::date))
Total runtime: 753467.847 ms

Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
415-422-1610

Tom Lane
<tgl@sss.pgh.pa.u
s> To
Patrick Hatcher
01/05/06 09:07 PM <PHatcher@macys.com>
cc
pgsql- performance@postgres
ql.org
Subject
Re: [PERFORM] Slow query. Any way
to speed up?

Patrick Hatcher <PHatcher@macys.com> writes:
> The following SQL takes 4+ mins to run. I have indexes on all join
fields
> and I've tried rearranging the table orders but haven't had any luck.

Please show EXPLAIN ANALYZE output, not just EXPLAIN. It's impossible
to tell whether the planner is making any wrong guesses when you can't
see the actual times/rowcounts ...

(BTW, 7.4 is looking pretty long in the tooth.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane

2006-01-06, 8:24 pm

Patrick Hatcher <PHatcher@macys.com> writes:
> -> Seq Scan on cdm_ddw_tran_item a1
> (cost=0.00..1547562.88 rows=8754773 width=23) (actual
> time=14.219..535704.691 rows=10838135 loops=1)
> Filter: ((((appl_id)::text = 'MCOM'::text)
> OR ((appl_id)::text = 'NET'::text)) AND ((tran_typ_id = 'S'::bpchar) OR
> (tran_typ_id = 'R'::bpchar)))

The bulk of the time is evidently going into this step. You didn't say
how big cdm_ddw_tran_item is, but unless it's in the billion-row range,
an indexscan isn't going to help for pulling out 10 million rows.
This may be about the best you can do :-(

If it *is* in the billion-row range, PG 8.1's bitmap indexscan facility
would probably help.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links