Gaetano,

> Hackers, what about to decrease the default values for this
> quantities ?

Oh, I don't think Ismail has a bug, I think he probably just needs to tune his 
database.

Ismail, join the PGSQL-PERFORMANCE mailing list and discuss this over there.   
-HACKERS is not the appropriate place for a tuning discussion.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 25 May 2004 10:34:27 -0700
Josh Berkus <josh@agliodbs.com>
Re: Optimizer bug??
Josh Berkus wrote:

> Gaetano,
> 
> 
>>Hackers, what about to decrease the default values for this
>>quantities ?
> 
> 
> Oh, I don't think Ismail has a bug, I think he probably just needs to tune his 
> database.

I just only suggesting to decrease that values that are oversized for a modern
hardware.


Regards
Gaetano Mendola




Tue, 25 May 2004 20:04:51 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Optimizer bug??
Thanks Josh,

I'll subscribe to that group.

Regards
Ismail Kizir
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: <pgsql-hackers@postgresql.org>
Cc: "Ismail Kizir" <ikizir@tumgazeteler.com>
Sent: Tuesday, May 25, 2004 8:34 PM
Subject: Re: [HACKERS] Optimizer bug??


> Gaetano,
>
> > Hackers, what about to decrease the default values for this
> > quantities ?
>
> Oh, I don't think Ismail has a bug, I think he probably just needs to tune
his
> database.
>
> Ismail, join the PGSQL-PERFORMANCE mailing list and discuss this over
there.
> -HACKERS is not the appropriate place for a tuning discussion.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>
>



Tue, 25 May 2004 21:20:21 +0300
"Ismail Kizir" <ikizir@tumgazeteler.com>
Re: Optimizer bug??
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Josh Berkus wrote:

| Gaetano,
|
|
|>I just only suggesting to decrease that values that are oversized for a
|
| modern
|
|>hardware.
|
|
| Hey, have you had success with those settings that you suggested?   I've tried
| tinkering with the relative CPU cost settings, and had mixed results.
| That's why I have no particular recommmendation for them.
|

Usually yes, decreasing that values I'm able to decrease the index scan
cost, so when I enable again the sequential scan the index one is choosed.


Regards
Gaetano Mendola




-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAs5Je7UpzwH2SGd4RAg/1AKCUYdTGIm5c7kG/ZXvmb49RWybs2ACgg3Wk
zOHkWnCvbyPgeDCU3pn6UfQ=
=Td0B
-----END PGP SIGNATURE-----


Tue, 25 May 2004 20:37:20 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Optimizer bug??
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

Tue, 25 May 2004 14:59:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Optimizer bug?? 
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
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  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














Tue, 25 May 2004 23:54:55 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Optimizer bug??