Gaetano Mendola <[EMAIL PROTECTED]> writes:
I just only suggesting to decrease that values that are oversized for a modern hardware.
I've seen no evidence saying that random_page_cost needs to be decreased for modern hardware. Disk seek speed versus bandwidth hasn't changed that much.
People sometimes find it profitable to decrease that setting to compensate for other optimizer issues, but that doesn't mean we ought to change the default.
As for the other settings you mentioned, I'd agree that the defaults are pretty arbitrary, but what evidence have you got to suggest better ones?
regards, tom lane
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
I had queries not using a index scan ( was the best method ) that started to use the index scan decreasing that values. What I also notice is that under certain values I'm not able to decrease anymore the cost of a query.
I'm using now:
random_page_cost = 2.0 cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.0025
vs these default costs:
#random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same)
look at this two queries (I just pick up one):
============ DEFAULT VALUES ================
test=# explain analyze select * from v_psr_guide_web; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan v_psr_guide_web (cost=750.58..750.85 rows=21 width=236) (actual time=196.420..197.210 rows=178 loops=1) -> Sort (cost=750.58..750.64 rows=21 width=236) (actual time=196.411..196.532 rows=178 loops=1) Sort Key: vg.estimated_start -> Hash Join (cost=717.57..750.12 rows=21 width=236) (actual time=190.489..195.817 rows=178 loops=1) Hash Cond: ("outer".id_publisher = "inner".id_publisher) -> Hash Left Join (cost=716.17..747.87 rows=128 width=208) (actual time=190.288..194.757 rows=178 loops=1) Hash Cond: ("outer".id_drm_service = "inner".id_drm_service) -> Hash Left Join (cost=715.12..746.15 rows=128 width=188) (actual time=189.978..193.734 rows=178 loops=1) Hash Cond: ("outer".id_cas_service = "inner".id_cas_service) -> Subquery Scan vg (cost=85.34..87.57 rows=127 width=168) (actual time=125.807..128.751 rows=178 loops=1) Filter: (view_target_group <> 2) -> Unique (cost=85.34..85.98 rows=127 width=324) (actual time=125.743..127.723 rows=192 loops=1) -> Sort (cost=85.34..85.66 rows=127 width=324) (actual time=125.738..126.573 rows=1298 loops=1) Sort Key: vp.id_package, s.estimated_start, sl.end_date -> Hash Join (cost=71.92..80.91 rows=127 width=324) (actual time=103.605..118.505 rows=1298 loops=1) Hash Cond: ("outer".id_package = "inner".id_package) -> Subquery Scan vp (cost=37.86..42.66 rows=384 width=304) (actual time=97.514..100.926 rows=384 loops=1) -> Sort (cost=37.86..38.82 rows=384 width=219) (actual time=97.488..97.744 rows=384 loops=1) Sort Key: p.id_publisher, p.name -> Hash Left Join (cost=1.96..21.37 rows=384 width=219) (actual time=1.003..95.690 rows=384 loops=1) Hash Cond: ("outer".id_package = "inner".id_package) -> Seq Scan on packages p (cost=0.00..13.84 rows=384 width=203) (actual time=0.005..0.780 rows=384 loops=1) -> Hash (cost=1.77..1.77 rows=77 width=20) (actual time=0.214..0.214 rows=0 loops=1) -> Seq Scan on package_security ps (cost=0.00..1.77 rows=77 width=20) (actual time=0.011..0.126 rows=77 loops=1) -> Hash (cost=33.81..33.81 rows=102 width=24) (actual time=5.756..5.756 rows=0 loops=1) -> Hash Join (cost=17.45..33.81 rows=102 width=24) (actual time=1.625..4.216 rows=1298 loops=1) Hash Cond: ("outer".id_program = "inner".id_program) -> Seq Scan on sequences s (cost=0.00..13.05 rows=305 width=16) (actual time=0.005..0.395 rows=305 loops=1) -> Hash (cost=17.42..17.42 rows=12 width=20) (actual time=1.230..1.230 rows=0 loops=1) -> Hash Join (cost=11.47..17.42 rows=12 width=20) (actual time=0.595..1.144 rows=69 loops=1) Hash Cond: ("outer".id_program = "inner".id_program) -> Seq Scan on slots sl (cost=0.00..4.55 rows=255 width=16) (actual time=0.005..0.248 rows=255 loops=1) -> Hash (cost=11.45..11.45 rows=9 width=4) (actual time=0.126..0.126 rows=0 loops=1) -> Seq Scan on programs pr (cost=0.00..11.45 rows=9 width=4) (actual time=0.046..0.102 rows=9 loops=1) Filter: (id_program_status <> 0) -> Hash (cost=563.82..563.82 rows=26382 width=28) (actual time=63.893..63.893 rows=0 loops=1) -> Seq Scan on cas_service cs (cost=0.00..563.82 rows=26382 width=28) (actual time=0.007..35.193 rows=26382 loops=1) -> Hash (cost=1.04..1.04 rows=4 width=28) (actual time=0.041..0.041 rows=0 loops=1) -> Seq Scan on drm_service ds (cost=0.00..1.04 rows=4 width=28) (actual time=0.008..0.014 rows=4 loops=1) -> Hash (cost=1.32..1.32 rows=32 width=36) (actual time=0.096..0.096 rows=0 loops=1) -> Seq Scan on publishers pub (cost=0.00..1.32 rows=32 width=36) (actual time=0.015..0.063 rows=32 loops=1) Total runtime: 198.590 ms (42 rows)
============ DECREASED VALUES ================
test=# explain analyze select * from v_psr_guide_web;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan v_psr_guide_web (cost=591.79..591.95 rows=21 width=236) (actual
time=130.301..131.085 rows=178 loops=1)
-> Sort (cost=591.79..591.85 rows=21 width=236) (actual time=130.291..130.401
rows=178 loops=1)
Sort Key: vg.estimated_start
-> Hash Join (cost=78.80..591.33 rows=21 width=236) (actual
time=123.451..129.753 rows=178 loops=1)
Hash Cond: ("outer".id_publisher = "inner".id_publisher)
-> Hash Left Join (cost=77.56..589.35 rows=128 width=208) (actual
time=123.245..128.666 rows=178 loops=1)
Hash Cond: ("outer".id_drm_service = "inner".id_drm_service)
-> Nested Loop Left Join (cost=76.53..587.66 rows=128
width=188) (actual time=123.104..127.781 rows=178 loops=1)
-> Subquery Scan vg (cost=76.53..78.12 rows=127
width=168) (actual time=123.080..126.108 rows=178 loops=1)
Filter: (view_target_group <> 2)
-> Unique (cost=76.53..77.16 rows=127 width=324)
(actual time=123.019..125.043 rows=192 loops=1)
-> Sort (cost=76.53..76.85 rows=127
width=324) (actual time=123.015..123.861 rows=1298 loops=1)
Sort Key: vp.id_package,
s.estimated_start, sl.end_date
-> Hash Join (cost=65.68..72.09
rows=127 width=324) (actual time=100.444..115.973 rows=1298 loops=1)
Hash Cond: ("outer".id_package =
"inner".id_package)
-> Subquery Scan vp
(cost=35.17..38.05 rows=384 width=304) (actual time=94.817..98.314 rows=384 loops=1)
-> Sort (cost=35.17..36.13
rows=384 width=219) (actual time=94.801..95.056 rows=384 loops=1)
Sort Key:
p.id_publisher, p.name
-> Hash Left Join
(cost=1.58..18.68 rows=384 width=219) (actual time=1.031..92.952 rows=384 loops=1)
Hash Cond:
("outer".id_package = "inner".id_package)
-> Seq Scan on
packages p (cost=0.00..11.92 rows=384 width=203) (actual time=0.005..0.717 rows=384
loops=1)
-> Hash
(cost=1.39..1.39 rows=77 width=20) (actual time=0.210..0.210 rows=0 loops=1)
-> Seq
Scan on package_security ps (cost=0.00..1.39 rows=77 width=20) (actual time=0.012..0.124
rows=77 loops=1)
-> Hash (cost=30.25..30.25
rows=102 width=24) (actual time=5.446..5.446 rows=0 loops=1)
-> Hash Join
(cost=15.93..30.25 rows=102 width=24) (actual time=1.280..3.979 rows=1298 loops=1)
Hash Cond: ("outer".id_program =
"inner".id_program)
-> Seq Scan on
sequences s (cost=0.00..11.53 rows=305 width=16) (actual time=0.005..0.409 rows=305
loops=1)
-> Hash
(cost=15.90..15.90 rows=12 width=20) (actual time=1.044..1.044 rows=0 loops=1)
-> Hash Join
(cost=11.29..15.90 rows=12 width=20) (actual time=0.381..0.923 rows=69 loops=1)
Hash Cond:
("outer".id_program = "inner".id_program)
-> Seq
Scan on slots sl (cost=0.00..3.27 rows=255 width=16) (actual time=0.005..0.247 rows=255
loops=1)
-> Hash
(cost=11.27..11.27 rows=9 width=4) (actual time=0.124..0.124 rows=0 loops=1)
->
Seq Scan on programs pr (cost=0.00..11.27 rows=9 width=4) (actual time=0.051..0.110
rows=9 loops=1)
Filter: (id_program_status <> 0)
-> Index Scan using cas_service_pkey on cas_service cs
(cost=0.00..4.00 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=178)
Index Cond: ("outer".id_cas_service =
cs.id_cas_service)
-> Hash (cost=1.02..1.02 rows=4 width=28) (actual
time=0.019..0.019 rows=0 loops=1)
-> Seq Scan on drm_service ds (cost=0.00..1.02 rows=4
width=28) (actual time=0.006..0.012 rows=4 loops=1)
-> Hash (cost=1.16..1.16 rows=32 width=36) (actual time=0.098..0.098
rows=0 loops=1)
-> Seq Scan on publishers pub (cost=0.00..1.16 rows=32
width=36) (actual time=0.016..0.064 rows=32 loops=1)
Total runtime: 132.000 ms
(41 rows)Just leaving the default values and decreasing the random_page_cost to 2.0 I get this:
test=# explain analyze select * from v_psr_guide_web;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan v_psr_guide_web (cost=602.88..603.14 rows=21 width=236) (actual
time=131.879..132.623 rows=178 loops=1)
-> Sort (cost=602.88..602.93 rows=21 width=236) (actual time=131.868..131.981
rows=178 loops=1)
Sort Key: vg.estimated_start
-> Hash Join (cost=87.79..602.42 rows=21 width=236) (actual
time=124.350..131.305 rows=178 loops=1)
Hash Cond: ("outer".id_publisher = "inner".id_publisher)
-> Hash Left Join (cost=86.39..600.17 rows=128 width=208) (actual
time=124.149..130.147 rows=178 loops=1)
Hash Cond: ("outer".id_drm_service = "inner".id_drm_service)
-> Nested Loop Left Join (cost=85.34..598.45 rows=128
width=188) (actual time=123.836..129.022 rows=178 loops=1)
-> Subquery Scan vg (cost=85.34..87.57 rows=127
width=168) (actual time=123.814..127.154 rows=178 loops=1)
Filter: (view_target_group <> 2)
-> Unique (cost=85.34..85.98 rows=127 width=324)
(actual time=123.752..125.949 rows=192 loops=1)
-> Sort (cost=85.34..85.66 rows=127
width=324) (actual time=123.747..124.608 rows=1298 loops=1)
Sort Key: vp.id_package,
s.estimated_start, sl.end_date
-> Hash Join (cost=71.92..80.91
rows=127 width=324) (actual time=101.402..116.651 rows=1298 loops=1)
Hash Cond: ("outer".id_package =
"inner".id_package)
-> Subquery Scan vp
(cost=37.86..42.66 rows=384 width=304) (actual time=95.264..98.797 rows=384 loops=1)
-> Sort (cost=37.86..38.82
rows=384 width=219) (actual time=95.249..95.499 rows=384 loops=1)
Sort Key:
p.id_publisher, p.name
-> Hash Left Join
(cost=1.96..21.37 rows=384 width=219) (actual time=1.026..93.442 rows=384 loops=1)
Hash Cond:
("outer".id_package = "inner".id_package)
-> Seq Scan on
packages p (cost=0.00..13.84 rows=384 width=203) (actual time=0.005..0.733 rows=384
loops=1)
-> Hash
(cost=1.77..1.77 rows=77 width=20) (actual time=0.212..0.212 rows=0 loops=1)
-> Seq
Scan on package_security ps (cost=0.00..1.77 rows=77 width=20) (actual time=0.012..0.124
rows=77 loops=1)
-> Hash (cost=33.81..33.81
rows=102 width=24) (actual time=5.806..5.806 rows=0 loops=1)
-> Hash Join
(cost=17.45..33.81 rows=102 width=24) (actual time=1.626..4.347 rows=1298 loops=1)
Hash Cond: ("outer".id_program =
"inner".id_program)
-> Seq Scan on
sequences s (cost=0.00..13.05 rows=305 width=16) (actual time=0.004..0.342 rows=305
loops=1)
-> Hash
(cost=17.42..17.42 rows=12 width=20) (actual time=1.240..1.240 rows=0 loops=1)
-> Hash Join
(cost=11.47..17.42 rows=12 width=20) (actual time=0.612..1.142 rows=69 loops=1)
Hash Cond:
("outer".id_program = "inner".id_program)
-> Seq
Scan on slots sl (cost=0.00..4.55 rows=255 width=16) (actual time=0.004..0.241 rows=255
loops=1)
-> Hash
(cost=11.45..11.45 rows=9 width=4) (actual time=0.129..0.129 rows=0 loops=1)
->
Seq Scan on programs pr (cost=0.00..11.45 rows=9 width=4) (actual time=0.043..0.104
rows=9 loops=1)
Filter: (id_program_status <> 0)
-> Index Scan using cas_service_pkey on cas_service cs
(cost=0.00..4.01 rows=1 width=28) (actual time=0.003..0.004 rows=0 loops=178)
Index Cond: ("outer".id_cas_service =
cs.id_cas_service)
-> Hash (cost=1.04..1.04 rows=4 width=28) (actual
time=0.044..0.044 rows=0 loops=1)
-> Seq Scan on drm_service ds (cost=0.00..1.04 rows=4
width=28) (actual time=0.008..0.016 rows=4 loops=1)
-> Hash (cost=1.32..1.32 rows=32 width=36) (actual time=0.096..0.096
rows=0 loops=1)
-> Seq Scan on publishers pub (cost=0.00..1.32 rows=32
width=36) (actual time=0.014..0.066 rows=32 loops=1)
Total runtime: 133.645 ms
(41 rows)
that is the same to the plan choosen with all values changed.
About the others value I was leaving that values each time I was able to decrease the cost and using an index scan then having a total runtime lower
I obtain that values after executing the queries at least 3 times; instead at the first shot I obtain:
Default values: average of 260 ms Decreased values: average of 150 ms
Regards Gaetano Mendola
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
