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

Sorry for the spam.
The 8.2.15 plan was on an empty database.
On a full database the plan was almost the same. So the question is
could I speed up the plan?
Why the "Hash Cond: (dp.person1_id = p.id)" isn't used for index scan on
that table?

Best regards,
Kaloya Iliev

Here is the plan on a full database:

==========================VERSION
8.2.17===================================================


version
---------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.17 on amd64-portbld-freebsd8.0, compiled by GCC cc (GCC)
4.2.1 20070719  [FreeBSD]
(1 row)

regbgrgr=# SHOW default_statistics_target ;
 default_statistics_target
---------------------------
 10
(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=48342.78..48342.79 rows=1 width=4) (actual
time=2429.190..2429.192 rows=1 loops=1)
   ->  Hash Join  (cost=5142.26..48339.54 rows=1295 width=4) (actual
time=314.817..2427.752 rows=570 loops=1)
         Hash Cond: (d.request_number = r.number)
         ->  Hash Join  (cost=3088.49..45960.01 rows=1308 width=8)
(actual time=37.001..2125.040 rows=570 loops=1)
               Hash Cond: (d.domain_status_id = ds.id)
               ->  Nested Loop  (cost=3064.88..45918.37 rows=1316
width=12) (actual time=35.584..2117.332 rows=1250 loops=1)
                     ->  Hash Join  (cost=3064.88..40159.12 rows=1316
width=4) (actual time=35.506..2043.384 rows=1250 loops=1)
                           Hash Cond: (dp.person1_id = p.id)
                           ->  Seq Scan on domainperson dp
(cost=0.00..36010.68 rows=285441 width=8) (actual time=0.069..1459.818
rows=274533 loops=1)
                                 Filter: (dp_type_id = 1)
                           ->  Hash  (cost=3048.93..3048.93 rows=1276
width=4) (actual time=35.206..35.206 rows=1157 loops=1)
                                 ->  Bitmap Heap Scan on person p
(cost=30.78..3048.93 rows=1276 width=4) (actual time=1.187..31.170
rows=1157 loops=1)
                                       Recheck Cond: ((lower(bulstat) =
'999999999'::text) OR (lower(bulstat) = 'bg999999999'::text))
                                       ->  BitmapOr  (cost=30.78..30.78
rows=1276 width=0) (actual time=0.841..0.841 rows=0 loops=1)
                                             ->  Bitmap Index Scan on
person_bulstat_lower_idx  (cost=0.00..25.28 rows=1199 width=0) (actual
time=0.709..0.709 rows=1135 loops=1)
                                                   Index Cond:
(lower(bulstat) = '999999999'::text)
                                             ->  Bitmap Index Scan on
person_bulstat_lower_idx  (cost=0.00..4.86 rows=77 width=0) (actual
time=0.124..0.124 rows=22 loops=1)
                                                   Index Cond:
(lower(bulstat) = 'bg999999999'::text)
                     ->  Index Scan using domeini_pkey on domeini d
(cost=0.00..4.36 rows=1 width=12) (actual time=0.043..0.046 rows=1
loops=1250)
                           Index Cond: (d.id = dp.domain_id)
               ->  Hash  (cost=21.31..21.31 rows=184 width=4) (actual
time=1.380..1.380 rows=184 loops=1)
                     ->  Seq Scan on domain_status ds  (cost=0.00..21.31
rows=184 width=4) (actual time=0.316..0.942 rows=184 loops=1)
                           Filter: (is_removed = 0)
         ->  Hash  (cost=1026.01..1026.01 rows=59101 width=4) (actual
time=277.161..277.161 rows=59027 loops=1)
               ->  Seq Scan on request r  (cost=0.00..1026.01 rows=59101
width=4) (actual time=0.075..131.951 rows=59027 loops=1)
 Total runtime: 2429.603 ms
(26 rows)


Kaloyan Iliev Iliev wrote:
> 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)
>
>