Query takes too long to run
Query takes too long to run
От:
"Devinder K Rajput" <Devinder.Rajput@ipaper.com>
Дата:
Hi all,
I am running the following query. It processes most of the locations fine
($Req_Store), however, on one of the locations, it takes almost 3 days to
run when it should take only about a half hour. (The query is rerun for
each location ($Req_Store)).
$result = $conn->exec("
declare my_cursor cursor for
select
invt_store,
g.minmindays
from dioh_iminvf_inventory a,
dioh_imitmf_itemmaster b,
dioh_imvitf_vendoritem c,
dioh_samohf_saleshistory d,
dioh_leaddays_loc f,
dioh_rank g,
dioh_rankcategory h
where
h.cp_item_cat = item_cat
and
g.rankid = h.rankid
and
g.rank = invt_rank
and
a.invt_store = '$Req_Store'
and
f.lead_store = '$Req_Store'
and
f.lead_vendno = c.vitm_vendno
and
(a.invt_store,a.invt_itemno) = (d.slht_store,d.slht_itemno)
and
b.item_itemno = a.invt_itemno
and
(b.item_itemno,b.item_vendno) = (c.vitm_itemno,c.vitm_vendno)
and
d.slht_monthid between $FromMonthId and $ToMonthId
order by invt_store, invt_itemno, slht_monthid
;
");
I setup logging by setting up:
debug_level = 2 # range 0-16
debug_print_query = true
debug_pretty_print = true
I get the following message about every 15 fifteen minutes in log file and
postmaster is using up close to 99% of the CPU.
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 22128 exited with status 0
Any ideas on what is going on. Thank you for your help.
Devinder Rajput
Stores Division Corporate Offices
Chicago, IL
(773) 442-6474
Re: Query takes too long to run
От:
"Devinder K Rajput" <Devinder.Rajput@ipaper.com>
Дата:
Indices:
dioh_iminvf_inventory ==> Indices: dioh_iminvf_idx_itemno,
dioh_iminvf_idx_store
dioh_imitmf_itemmaster ==> Indices: dioh_imitmf_idx_itemno,
dioh_imitmf_idx_vendno
dioh_imvitf_vendoritem ==> Indices: dioh_imvitf_idx_itemno,
dioh_imvitf_idx_vendno
dioh_samohf_saleshistory ==> Indices: dioh_samohf_idx_itemno,
dioh_samohf_idx_monthid, dioh_samohf_idx_store
dioh_leaddays_loc ==> Index: dioh_leaddays_loc_idx_vendnoloc
dioh_rank ==> Indices: None (table is only 12 rows)
dioh_rankcategory ==> Indices: None (table is only 22 rows)
NOTICE: QUERY PLAN:
Sort (cost=89.13..89.13 rows=1 width=300)
-> Nested Loop (cost=0.00..89.12 rows=1 width=300)
-> Nested Loop (cost=0.00..86.43 rows=1 width=284)
-> Nested Loop (cost=0.00..82.29 rows=1 width=212)
-> Nested Loop (cost=0.00..49.79 rows=1 width=188)
-> Nested Loop (cost=0.00..48.52 rows=1
width=152)
-> Nested Loop (cost=0.00..34.10 rows=1
width=104)
-> Index Scan using
dioh_samohf_idx_monthid on dioh_samohf_saleshistory d (cost=0.00..5.01
rows=1 width=44)
-> Index Scan using
dioh_iminvf_idx_store on dioh_iminvf_inventory a (cost=0.00..29.00 rows=6
width=60)
-> Index Scan using dioh_imvitf_idx_itemno
on dioh_imvitf_vendoritem c (cost=0.00..14.36 rows=4 width=48)
-> Seq Scan on dioh_rank g (cost=0.00..1.12
rows=12 width=36)
-> Seq Scan on dioh_rankcategory h (cost=0.00..20.00
rows=1000 width=24)
-> Index Scan using dioh_imitmf_idx_itemno on
dioh_imitmf_itemmaster b (cost=0.00..4.12 rows=1 width=72)
-> Index Scan using dioh_leaddays_loc_idx_vendnoloc on
dioh_leaddays_loc f (cost=0.00..2.68 rows=1 width=16)
EXPLAIN
I see that a sequential scan is being done on the dioh_rank and
dioh_rankcategory tables and could be indexed. However, this query runs
for about 80 locations in only about half an hour, but takes almost 80
hours for one location. Thank you for your help,
Devinder Rajput
Stores Division Corporate Offices
Chicago, IL
(773) 442-6474
"Josh
Goldberg" To: "Devinder K Rajput"
Subject: Re: [ADMIN] Query takes too long to run
10/07/2002
05:34 PM
which columns are indexed? what do you see when you do explain/explain
analyze?
----- Original Message -----
From: "Devinder K Rajput"
To:
Sent: Monday, October 07, 2002 3:20 PM
Subject: [ADMIN] Query takes too long to run
> Hi all,
>
> I am running the following query. It processes most of the locations
fine
> ($Req_Store), however, on one of the locations, it takes almost 3 days to
> run when it should take only about a half hour. (The query is rerun for
> each location ($Req_Store)).
>
> $result = $conn->exec("
> declare my_cursor cursor for
> select
> invt_store,
>
> g.minmindays
> from dioh_iminvf_inventory a,
> dioh_imitmf_itemmaster b,
> dioh_imvitf_vendoritem c,
> dioh_samohf_saleshistory d,
> dioh_leaddays_loc f,
> dioh_rank g,
> dioh_rankcategory h
> where
> h.cp_item_cat = item_cat
> and
> g.rankid = h.rankid
> and
> g.rank = invt_rank
> and
> a.invt_store = '$Req_Store'
> and
> f.lead_store = '$Req_Store'
> and
> f.lead_vendno = c.vitm_vendno
> and
> (a.invt_store,a.invt_itemno) = (d.slht_store,d.slht_itemno)
> and
> b.item_itemno = a.invt_itemno
> and
> (b.item_itemno,b.item_vendno) = (c.vitm_itemno,c.vitm_vendno)
> and
> d.slht_monthid between $FromMonthId and $ToMonthId
> order by invt_store, invt_itemno, slht_monthid
> ;
> ");
>
> I setup logging by setting up:
> debug_level = 2 # range 0-16
> debug_print_query = true
> debug_pretty_print = true
>
> I get the following message about every 15 fifteen minutes in log file
and
> postmaster is using up close to 99% of the CPU.
> DEBUG: proc_exit(0)
> DEBUG: shmem_exit(0)
> DEBUG: exit(0)
> /usr/bin/postmaster: reaping dead processes...
> /usr/bin/postmaster: CleanupProc: pid 22128 exited with status 0
>
> Any ideas on what is going on. Thank you for your help.
>
> Devinder Rajput
> Stores Division Corporate Offices
> Chicago, IL
> (773) 442-6474
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>