X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 97BE23A492F for ; Fri, 19 Nov 2004 22:32:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30010-05 for ; Fri, 19 Nov 2004 22:32:45 +0000 (GMT) Received: from web51301.mail.yahoo.com (web51301.mail.yahoo.com [206.190.38.167]) by svr1.postgresql.org (Postfix) with SMTP id 29A033A3D19 for ; Fri, 19 Nov 2004 22:31:23 +0000 (GMT) Received: (qmail 49472 invoked by uid 60001); 19 Nov 2004 22:31:22 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=W4lYflwRYaf1irkr27hTlOGkGBjA9ZxAk9ZB2k49CiNzA2VT6Yt+AQVCvZC7QpWf9iaPvibVEI0lVZjL4UfXhg6ytr4iC1xte7KMdymUjQ69SmbI5DbAT/DYNzjG4H56n15Rxp7G2Ub5MknjxlRX4z99yMfMaiAm+tJT/zqFxUg= ; Message-ID: <20041119223122.49470.qmail@web51301.mail.yahoo.com> Received: from [65.218.182.242] by web51301.mail.yahoo.com via HTTP; Fri, 19 Nov 2004 14:31:22 PST Date: Fri, 19 Nov 2004 14:31:22 -0800 (PST) From: sarlav kumar Subject: help needed -- sequential scan problem To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1225906050-1100903482=:47357" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200411/305 X-Sequence-Number: 9259 --0-1225906050-1100903482=:47357 Content-Type: text/plain; charset=us-ascii 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! --0-1225906050-1100903482=:47357 Content-Type: text/html; charset=us-ascii
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! --0-1225906050-1100903482=:47357--