Re: Optimizer bug?? - Mailing list pgsql-hackers

Tom Lane wrote:
> Gaetano Mendola <mendola@bigfoot.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
 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SubqueryScan 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.87rows=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.751rows=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=1298loops=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)
(actualtime=0.011..0.126 rows=77 loops=1)                                                   ->  Hash
(cost=33.81..33.81rows=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)
(actualtime=0.005..0.395 rows=305 loops=1)                                                               ->  Hash
(cost=17.42..17.42rows=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.55rows=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.45rows=9 width=4) (actual time=0.046..0.102 rows=9 loops=1)
                                        Filter: (id_program_status <> 0)                           ->  Hash
(cost=563.82..563.82rows=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)                     ->
SeqScan 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.590ms
 
(42 rows)


============ DECREASED VALUES ================

test=# explain analyze select * from v_psr_guide_web;
                      QUERY PLAN
 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SubqueryScan 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.35rows=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=128width=188) (actual time=123.104..127.781 rows=178 loops=1)                           ->  Subquery Scan vg
(cost=76.53..78.12rows=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)
(actualtime=123.019..125.043 rows=192 loops=1)                                       ->  Sort  (cost=76.53..76.85
rows=127width=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.09rows=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=384loops=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.952rows=384 loops=1)                                                                     Hash Cond:
("outer".id_package= "inner".id_package)                                                                     ->  Seq
Scanon 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=0loops=1)                                                                           ->  Seq Scan on
package_securityps  (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.979rows=1298 loops=1)                                                               Hash Cond:
("outer".id_program= "inner".id_program)                                                               ->  Seq Scan on
sequencess  (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)
(actualtime=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.003rows=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.012rows=4 loops=1)               ->  Hash  (cost=1.16..1.16 rows=32 width=36) (actual time=0.098..0.098
rows=0loops=1)                     ->  Seq Scan on publishers pub  (cost=0.00..1.16 rows=32 width=36) (actual
time=0.016..0.064rows=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
 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SubqueryScan 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.17rows=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=128width=188) (actual time=123.836..129.022 rows=178 loops=1)                           ->  Subquery Scan vg
(cost=85.34..87.57rows=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)
(actualtime=123.752..125.949 rows=192 loops=1)                                       ->  Sort  (cost=85.34..85.66
rows=127width=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.91rows=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=384loops=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.442rows=384 loops=1)                                                                     Hash Cond:
("outer".id_package= "inner".id_package)                                                                     ->  Seq
Scanon 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=0loops=1)                                                                           ->  Seq Scan on
package_securityps  (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.347rows=1298 loops=1)                                                               Hash Cond:
("outer".id_program= "inner".id_program)                                                               ->  Seq Scan on
sequencess  (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)
(actualtime=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.004rows=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.016rows=4 loops=1)               ->  Hash  (cost=1.32..1.32 rows=32 width=36) (actual time=0.096..0.096
rows=0loops=1)                     ->  Seq Scan on publishers pub  (cost=0.00..1.32 rows=32 width=36) (actual
time=0.014..0.066rows=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