Re: Optimizer bug??

Tom Lane wrote:
> Gaetano Mendola <mendola(at)bigfoot(dot)com> 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