Re: Question about LEFT JOIN and query plan
От | Kaloyan Iliev Iliev |
---|---|
Тема | Re: Question about LEFT JOIN and query plan |
Дата | |
Msg-id | 4C863E06.2020201@digsys.bg обсуждение исходный текст |
Ответ на | Re: Question about LEFT JOIN and query plan (Kaloyan Iliev Iliev <kaloyan@digsys.bg>) |
Ответы |
Re: Question about LEFT JOIN and query plan
("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
|
Список | 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) > >
В списке pgsql-performance по дате отправления: