Re: 7.2.1 optimises very badly against 7.2 - Mailing list pgsql-general

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 08 July 2002 21:44
> To: Sam Liddicott
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 7.2.1 optimises very badly against 7.2
>
> If you turn off enable_seqscan, what EXPLAIN results do you get from
> 7.2.1?

Wow.  Even better than 7.2, much better use of indexes.

Unique  (cost=116936.99..117348.73 rows=531 width=276) (actual
time=567.37..571.80 rows=305 loops=1)
  ->  Sort  (cost=116936.99..116936.99 rows=5313 width=276) (actual
time=567.36..567.70 rows=305 loops=1)
        ->  Nested Loop  (cost=3.51..116608.26 rows=5313 width=276) (actual
time=6.56..561.30 rows=305 loops=1)
              ->  Nested Loop  (cost=3.51..92080.31 rows=5313 width=119)
(actual time=6.00..519.18 rows=305 loops=1)
                    ->  Hash Join  (cost=3.51..61.71 rows=9 width=84)
(actual time=1.45..7.61 rows=9 loops=1)
                          ->  Index Scan using channelregion_pkey,
channelregion_pkey, channelregion_pkey, channelregion_pkey,
channelregion_pkey, channelregion_pkey, channelregion_pkey,
channelregion_pkey, channelregion_pkey on channelregion  (cost=0.00..58.05
rows=9 width=60) (actual time=0.17..5.78 rows=9 loops=1)
                          ->  Hash  (cost=3.49..3.49 rows=7 width=24)
(actual time=0.14..0.14 rows=0 loops=1)
                                ->  Index Scan using distribution_pkey on
distribution  (cost=0.00..3.49 rows=7 width=24) (actual time=0.06..0.11
rows=7 loops=1)
                    ->  Index Scan using idx_broadcast_channelregionid on
broadcast  (cost=0.00..10273.33 rows=872 width=35) (actual time=3.75..56.22
rows=34 loops=9)
              ->  Index Scan using episode_pkey on episode  (cost=0.00..4.60
rows=1 width=157) (actual time=0.09..0.10 rows=1 loops=305)
Total runtime: 668.73 msec


> How about if you leave enable_seqscan on, but reduce
> random_page_cost from the default 4.0 to perhaps 3.0?

The same.  Normally (in high concurrent use) I think even sequence scans
degrade to random_pages because of disk head contention; but in this case
the seq_scan query was running on an otherwise idle machine, as the sole
pgsql client, so perhaps as you hinted the random_page_cost was too high?
But I feel where indexes are used and seq_scan *could* have been used,
seq_scan is only slightly faster where the machine is idle (and the small
delay can perhaps be afforded), but where there there is disk head
contention seq_scan is deadly, thus I always prefer index scan, so I shall
disable seq_scan in the config file.  Is my reasoning faulty?, and is it a
reasonable solution or can we expect that the query-plan-chooser ought
always to do better?

Thanks for you help on this and I'm happy to keep running tests for you
until you are happy with your results as well as me happy with mine.

Unique  (cost=91354.47..91766.20 rows=531 width=276) (actual
time=542.55..547.01 rows=305 loops=1)
  ->  Sort  (cost=91354.47..91354.47 rows=5313 width=276) (actual
time=542.54..542.89 rows=305 loops=1)
        ->  Nested Loop  (cost=1.09..91025.74 rows=5313 width=276) (actual
time=7.14..536.40 rows=305 loops=1)
              ->  Nested Loop  (cost=1.09..69315.01 rows=5313 width=119)
(actual time=6.59..496.13 rows=305 loops=1)
                    ->  Hash Join  (cost=1.09..50.53 rows=9 width=84)
(actual time=1.94..9.11 rows=9 loops=1)
                          ->  Index Scan using channelregion_pkey,
channelregion_pkey, channelregion_pkey, channelregion_pkey,
channelregion_pkey, channelregion_pkey, channelregion_pkey,
channelregion_pkey, channelregion_pkey on channelregion  (cost=0.00..49.28
rows=9 width=60) (actual time=0.42..7.04 rows=9 loops=1)
                          ->  Hash  (cost=1.07..1.07 rows=7 width=24)
(actual time=0.20..0.20 rows=0 loops=1)
                                ->  Seq Scan on distribution
(cost=0.00..1.07 rows=7 width=24) (actual time=0.13..0.16 rows=7 loops=1)
                    ->  Index Scan using idx_broadcast_channelregionid on
broadcast  (cost=0.00..7730.28 rows=872 width=35) (actual time=3.66..53.50
rows=34 loops=9)
              ->  Index Scan using episode_pkey on episode  (cost=0.00..4.07
rows=1 width=157) (actual time=0.09..0.10 rows=1 loops=305)
Total runtime: 630.32 msec


Sam