Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1AE6C633110 for ; Tue, 7 Sep 2010 10:13:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 29782-08 for ; Tue, 7 Sep 2010 13:13:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from relay2.digsys.bg (varna.digsys.bg [192.92.129.9]) by mail.postgresql.org (Postfix) with ESMTP id B04E06336F9 for ; Tue, 7 Sep 2010 10:13:05 -0300 (ADT) Received: from luke.digsys.bg (kaloyan@luke.digsys.bg [193.68.6.22]) by relay2.digsys.bg (8.14.4/8.14.4) with ESMTP id o87DD19m029861; Tue, 7 Sep 2010 16:13:02 +0300 (EEST) (envelope-from kaloyan@digsys.bg) Message-ID: <4C863A5D.1030900@digsys.bg> Date: Tue, 07 Sep 2010 16:13:01 +0300 From: Kaloyan Iliev Iliev Reply-To: kaloyan@digsys.bg Organization: Register.BG Ltd. User-Agent: Thunderbird 2.0.0.24 (X11/20100614) MIME-Version: 1.0 To: Tom Lane , pgsql-performance@postgresql.org Subject: Re: Question about LEFT JOIN and query plan References: <4C811F68.9060601@digsys.bg> <25498.1283534083@sss.pgh.pa.us> In-Reply-To: <25498.1283534083@sss.pgh.pa.us> Content-Type: text/plain; charset=windows-1251; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/24 X-Sequence-Number: 40227 Hello again, I have another query which performance drops drastically after PG upgrade. I can not improve the plan no matter how hard I try. I try creating new indexes and rewrite the query with JOIN .. ON instead of commas but nothing happens. I will appreciate any suggestions. Best regards, Kaloyan Iliev ==========================VERSION 8.2.15=================================================== regbgrgr=# SELECT version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 8.2.15 on amd64-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD] (1 row) regbgrgr=# explain analyze SELECT COUNT (D.id) as all_domains_count FROM domeini as D, domainperson as DP, person as P, request as R, domain_status as DS WHERE R.number = D.request_number AND D.domain_status_id = DS.id AND DS.is_removed = 0 AND D.id = DP.domain_id AND DP.dp_type_id = 1 AND DP.person1_id = P.id AND ( LOWER (P.bulstat) = LOWER ('999999999') OR LOWER (P.bulstat) = 'bg'||'999999999'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=138.30..138.31 rows=1 width=4) (actual time=0.804..0.806 rows=1 loops=1) -> Nested Loop (cost=74.70..138.29 rows=5 width=4) (actual time=0.797..0.797 rows=0 loops=1) -> Nested Loop (cost=74.70..136.88 rows=5 width=8) (actual time=0.793..0.793 rows=0 loops=1) -> Nested Loop (cost=74.70..135.44 rows=5 width=12) (actual time=0.791..0.791 rows=0 loops=1) -> Hash Join (cost=74.70..122.42 rows=5 width=4) (actual time=0.787..0.787 rows=0 loops=1) Hash Cond: (dp.person1_id = p.id) -> Bitmap Heap Scan on domainperson dp (cost=19.91..65.81 rows=472 width=8) (actual time=0.088..0.088 rows=1 loops=1) Recheck Cond: (dp_type_id = 1) -> Bitmap Index Scan on domainperson_admin_person_uidx (cost=0.00..19.79 rows=472 width=0) (actual time=0.071..0.071 rows=474 loops=1) Index Cond: (dp_type_id = 1) -> Hash (cost=54.62..54.62 rows=14 width=4) (actual time=0.678..0.678 rows=0 loops=1) -> Seq Scan on person p (cost=0.00..54.62 rows=14 width=4) (actual time=0.675..0.675 rows=0 loops=1) Filter: ((lower(bulstat) = '999999999'::text) OR (lower(bulstat) = 'bg999999999'::text)) -> Index Scan using domeini_pkey on domeini d (cost=0.00..2.59 rows=1 width=12) (never executed) Index Cond: (d.id = dp.domain_id) -> Index Scan using domain_status_pkey on domain_status ds (cost=0.00..0.27 rows=1 width=4) (never executed) Index Cond: (d.domain_status_id = ds.id) Filter: (is_removed = 0) -> Index Scan using request_pkey on request r (cost=0.00..0.27 rows=1 width=4) (never executed) Index Cond: (r.number = d.request_number) Total runtime: 0.926 ms (21 rows) regbgrgr=# SHOW default_statistics_target ; default_statistics_target --------------------------- 10 (1 row) ==========================VERSION 8.4.4=================================================== regbgrgr=# select version (); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 8.4.4 on amd64-portbld-freebsd8.1, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit (1 row) regbgrgr=# explain analyze SELECT COUNT (D.id) as all_domains_count FROM domeini as D, domainperson as DP, person as P, request as R, domain_status as DS WHERE R.number = D.request_number AND D.domain_status_id = DS.id AND DS.is_removed = 0 AND D.id = DP.domain_id AND DP.dp_type_id = 1 AND DP.person1_id = P.id AND ( LOWER (P.bulstat) = LOWER ('999999999') OR LOWER (P.bulstat) = 'bg'||'999999999'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=61113.19..61113.20 rows=1 width=4) (actual time=6013.705..6013.706 rows=1 loops=1) -> Hash Join (cost=20859.23..61023.00 rows=36075 width=4) (actual time=4553.945..6013.098 rows=598 loops=1) Hash Cond: (d.request_number = r.number) -> Hash Join (cost=18796.01..57800.47 rows=36075 width=8) (actual time=4177.313..5646.153 rows=598 loops=1) Hash Cond: (d.domain_status_id = ds.id) -> Hash Join (cost=18778.40..57286.82 rows=36075 width=12) (actual time=4176.838..5643.637 rows=1357 loops=1) Hash Cond: (dp.domain_id = d.id) -> Hash Join (cost=4671.42..40710.39 rows=36080 width=4) (actual time=3210.201..4621.977 rows=1357 loops=1) Hash Cond: (dp.person1_id = p.id) -> Seq Scan on domainperson dp (cost=0.00..33976.29 rows=272302 width=8) (actual time=0.026..1128.230 rows=279008 loops=1) Filter: (dp_type_id = 1) -> Hash (cost=4634.39..4634.39 rows=2962 width=4) (actual time=3210.050..3210.050 rows=1263 loops=1) -> Bitmap Heap Scan on person p (cost=64.33..4634.39 rows=2962 width=4) (actual time=114.401..3206.440 rows=1263 loops=1) Recheck Cond: ((lower(bulstat) = '999999999'::text) OR (lower(bulstat) = 'bg999999999'::text)) -> BitmapOr (cost=64.33..64.33 rows=2969 width=0) (actual time=95.115..95.115 rows=0 loops=1) -> Bitmap Index Scan on person_bulstat_lower_idx (cost=0.00..31.43 rows=1485 width=0) (actual time=33.525..33.525 rows=1241 loops=1) Index Cond: (lower(bulstat) = '999999999'::text) -> Bitmap Index Scan on person_bulstat_lower_idx (cost=0.00..31.43 rows=1485 width=0) (actual time=61.584..61.584 rows=22 loops=1) Index Cond: (lower(bulstat) = 'bg999999999'::text) -> Hash (cost=8728.77..8728.77 rows=309377 width=12) (actual time=957.267..957.267 rows=309410 loops=1) -> Seq Scan on domeini d (cost=0.00..8728.77 rows=309377 width=12) (actual time=0.015..563.414 rows=309410 loops=1) -> Hash (cost=15.31..15.31 rows=184 width=4) (actual time=0.455..0.455 rows=184 loops=1) -> Seq Scan on domain_status ds (cost=0.00..15.31 rows=184 width=4) (actual time=0.009..0.252 rows=184 loops=1) Filter: (is_removed = 0) -> Hash (cost=1030.43..1030.43 rows=62943 width=4) (actual time=356.134..356.134 rows=62815 loops=1) -> Seq Scan on request r (cost=0.00..1030.43 rows=62943 width=4) (actual time=10.902..275.137 rows=62815 loops=1) Total runtime: 6014.029 ms (27 rows) regbgrgr=# show default_statistics_target ; default_statistics_target --------------------------- 100 (1 row)