Re: Query takes too long to run - Mailing list pgsql-admin

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" <Devinder.Rajput@ipaper.com>
                    <josh@4dmatrix       cc:
                    .com>                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" <Devinder.Rajput@ipaper.com>
To: <pgsql-admin@postgresql.org>
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,
>     <snip>
>     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)
>