Re: Question about LEFT JOIN and query plan - Mailing list pgsql-performance

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)