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)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
