pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: sarlav kumar <sarlavk@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: help needed -- sequential scan problem
Date: Fri, 19 Nov 2004 14:31:22 -0800 (PST)
Message-ID: <20041119223122.49470.qmail@web51301.mail.yahoo.com> (raw)

Hi All,
 
I am new to Postgres.
 
I have a query which does not use index scan unless I force postgres to use index scan. I dont want to force postgres, unless there is no way of optimizing this query.
 
The query :
 
select m.company_name,m.approved,cu.account_no,mbt.business_name,cda.country, 
(select count(*) from merchant_purchase mp left join data d on mp.data_id=d.id where mp.merchant_id=m.id and d.status=5) as Trans_count,
(select sum(total * 0.01) from merchant_purchase mp left join data d on mp.data_id=d.id where mp.merchant_id=m.id and d.status=5) as Trans_amount,
(select count(*) from merchant_purchase mp left join data d on mp.data_id=d.id where d.what=15 and d.status=5 and d.flags=7 and mp.merchant_id=m.id) as Reversal_count
from merchant m 
left join customer cu on cu.id=m.uid 
left join customerdata cda on cda.uid=cu.id 
left join merchant_business_types mbt on mbt.id=m.businesstype and
exists (select distinct(merchant_id) from merchant_purchase where m.id=merchant_id);

 
First Question: I know the way I have written the first two sub-selects is really bad, as they have the same conditions in the where clause. But I am not sure if there is a way to select two columns in a single sub-select query. When I tried to combine the two sub-select queries, I got an error saying that the sub-select can have only one column. Does anyone know any other efficient way of doing it?
 
Second Question: The query plan is as follows:
 
 QUERY PLAN                                                      
                        
-------------------------------------------------------------------------------------------------------------------------------- Hash Join  (cost=901.98..17063.67 rows=619 width=88) (actual time=52.01..5168.09 rows=619 loops=1)
   Hash Cond: ("outer".businesstype = "inner".id)
   Join Filter: (subplan)
   ->  Merge Join  (cost=900.34..1276.04 rows=619 width=62) (actual time=37.00..97.58 rows=619 loops=1)
         Merge Cond: ("outer".id = "inner".uid)
         ->  Merge Join  (cost=900.34..940.61 rows=619 width=52) (actual time=36.91..54.66 rows=619 loops=1)
               Merge Cond: ("outer".id = "inner".uid)
               ->  Sort  (cost=795.45..810.32 rows=5949 width=17) (actual time=32.59..36.59 rows=5964 loops=1)
                     Sort Key: cu.id
                     ->  Seq Scan on customer cu  (cost=0.00..422.49 rows=5949 width=17) (actual time=0.02..15.69 rows=5964 loops=1)
               ->  Sort  (cost=104.89..106.44 rows=619 width=35) (actual time=4.27..5.10 rows=619 loops=1)
                     Sort Key: m.uid
                     ->  Seq Scan on merchant m  (cost=0.00..76.19 rows=619 width=35) (actual time=0.04..2.65 rows=619 loops=1)
         ->  Index Scan using customerdata_uid_idx on customerdata cda  (cost=0.00..311.85 rows=5914 width=10) (actual time=0.09..27.70 rows=5
919 loops=1)
   ->  Hash  (cost=1.51..1.51 rows=51 width=26) (actual time=0.19..0.19 rows=0 loops=1)
         ->  Seq Scan o n merchant_business_types mbt  (cost=0.00..1.51 rows=51 width=26) (actual time=0.04..0.12 rows=51 loops=1)
   SubPlan
     ->  Aggregate  (cost=269.89..269.89 rows=1 width=12) (actual time=2.70..2.70 rows=1 loops=619)
           ->  Nested Loop  (cost=0.00..269.78 rows=44 width=12) (actual time=2.40..2.69 rows=4 loops=619)
                 Filter: ("inner".status = 5)
                 ->  Seq Scan on merchant_purchase mp  (cost=0.00..95.39 rows=44 width=4) (actual time=2.37..2.58 rows=6 loops=619)
                       Filter: (merchant_id = $0)
                 ->  Index Scan using data_pkey on data d  (cost=0.00..3.91 rows=1 width=8) (actual time=0.01..0.01 rows=1 loops=3951)
                       Index Cond: ("outer".data_id = d.id)
     ->  Aggregate  (cost=269.89..269.89 rows=1 width=16) (actual time=2.73..2.73 rows=1 loops=619)
           ->  Nested Loop  (cost=0.00..269.78 rows=44 width=16) (actual time=2.42..2.70 rows=4 loops=619)
                 Filter: ("inner".status = 5)
                 ->  Seq Scan on merchant_purchase m p  (cost=0.00..95.39 rows=44 width=8) (actual time=2.39..2.60 rows=6 loops=619)
                       Filter: (merchant_id = $0)
                 ->  Index Scan using data_pkey on data d  (cost=0.00..3.91 rows=1 width=8) (actual time=0.01..0.01 rows=1 loops=3951)
                       Index Cond: ("outer".data_id = d.id)
     ->  Aggregate  (cost=270.12..270.12 rows=1 width=20) (actual time=2.72..2.72 rows=1 loops=619)
           ->  Nested Loop  (cost=0.00..270.00 rows=44 width=20) (actual time=2.63..2.72 rows=0 loops=619)
                 Filter: (("inner".what = 15) AND ("inner".status = 5) AND ("inner".flags = 7))
                 ->  Seq Scan on merchant_purchase mp  (cost=0.00..95.39 rows=44 width=4) (actual time=2.40..2.62 rows=6 loops=619)
                       Filter: (merchant_id = $0)
                 ->  Index Scan using data_pkey on data d  (cost=0.00..3.91 rows=1 width=16) (actual time=0.01..0.01 rows=1 loops=3951)
                       Index Cond: ("outer".data_id = d.id)
     ->  Unique  (cost=0.00..113.14 rows=4 width=4) (actual time=0.02..0.02 rows=0 loops=598)
           ->  Index Scan using merchant_purchase_merchant_id_idx on merchant_purchase  (cost=0.00..113.02 rows=44 width=4) (actual time=0.01.
.0.01 rows=0 loops=598)
                 Index Cond: ($0 = merchant_id)
 Total runtime: 5170.37 msec (5.170 sec)
(42 rows)

 
As you can see, there are many sequential scans in the query plan. Postgres is not using the index defined, even though it leads to better performance(0.2 sec!! when i force index scan)
 
Is there something wrong in my query that makes postgres use seq scan as opposed to index scan?? Any help would be really appreciated.
 
Thanks for you time and help!
Saranya

		
---------------------------------
Do you Yahoo!?
 Discover all thatÂ’s new in My Yahoo!

view thread (6+ messages)  latest in thread

Message-ID: <20041119223122.49470.qmail@web51301.mail.yahoo.com>
Permalink:  ../20041119223122.49470.qmail@web51301.mail.yahoo.com/
Also on:    postgresql.org/message-id/20041119223122.49470.qmail@web51301.mail.yahoo.com

 · 

reply

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: sarlavk@yahoo.com
  Subject: Re: help needed -- sequential scan problem
  In-Reply-To: <20041119223122.49470.qmail@web51301.mail.yahoo.com>

* 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