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

A bit late, but here is the explain analyse data for the problem where a
complex DB was able to produce very quick results with low load with
postgres 7.2 but takes many many times longer with postgres 7.2.1.

O notice missing in the 7.2.1 (slow) explain analyse this part:
"Index Scan using idx_broadcast_channelregionid on broadcast"

Here is the query:

SELECT distinct
      channelregion."id",
      channelregion."customtitle",
      channelregion."title" as channeltitle,
      channelregion."tag" as channeltag,
      channelregion."distributionid",
      channelregion."channelid",

      distribution."description",

      broadcast."id" as broadcastid,
      broadcast."groupid",
      broadcast."duration",
      broadcast."start" as stime,
      broadcast."stereo",
      broadcast."subtitles" as subtitle,
      broadcast."repeat",
      broadcast."blackandwhite" as bw,
      broadcast."premiere",
      broadcast."surround",
      broadcast."widescreen",
      broadcast."followon",

      episode."id" as episodeid,
      episode."title" as title,
      episode."seriestitle" as seriestitle,
      episode."categories",
      episode."episodename",
      episode."episodereference",
      episode."episodenumber",
      episode."episodecount",
      episode."detail0",
      episode."detail1",
      episode."detail2",
      episode."created" as filmyear
INTO TEMPORARY TABLE selection
FROM
    "channelregion" channelregion,
    "broadcast" broadcast,
    "distribution" distribution,
    "episode" episode
WHERE
    broadcast.channelregionid=channelregion.id AND
    channelregion."distributionid" = distribution."id" AND
    broadcast.episode=episode.id AND
    (((broadcast.start+broadcast.duration)>1026120300) AND
(broadcast.sourcekey<=20020708) AND
((channelregion.id in (2,20,41,53,54,733,734,86,33))))
ORDER BY
    broadcast."start" ASC;

Here is explain analyse on a postgres 7.2.1 box:
Unique  (cost=99202.15..99607.55 rows=523 width=279) (actual
time=7932.43..7936.36 rows=276 loops=1)
  ->  Sort  (cost=99202.15..99202.15 rows=5231 width=279) (actual
time=7932.41..7932.73 rows=276 loops=1)
        ->  Nested Loop  (cost=78.02..98879.06 rows=5231 width=279) (actual
time=2779.61..7926.74 rows=276 loops=1)
              ->  Hash Join  (cost=78.02..74013.87 rows=5231 width=119)
(actual time=2778.98..7886.85 rows=276 loops=1)
                    ->  Seq Scan on broadcast  (cost=0.00..70871.32
rows=399885 width=35) (actual time=2768.80..7851.94 rows=8019 loops=1)
                    ->  Hash  (cost=78.00..78.00 rows=9 width=84) (actual
time=9.56..9.56 rows=0 loops=1)
                          ->  Hash Join  (cost=1.09..78.00 rows=9 width=84)
(actual time=1.73..9.53 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..76.75
rows=9 width=60) (actual time=0.48..8.00 rows=9 loops=1)
                                ->  Hash  (cost=1.07..1.07 rows=7 width=24)
(actual time=0.19..0.19 rows=0 loops=1)
                                      ->  Seq Scan on distribution
(cost=0.00..1.07 rows=7 width=24) (actual time=0.14..0.17 rows=7 loops=1)
              ->  Index Scan using episode_pkey on episode  (cost=0.00..4.74
rows=1 width=160) (actual time=0.09..0.11 rows=1 loops=276)
Total runtime: 8009.97 msec


Here is explain analyse on a 7.2 box:
Unique  (cost=13355.63..13416.75 rows=79 width=278) (actual
time=525.79..529.63 rows=276 loops=1)
  ->  Sort  (cost=13355.63..13355.63 rows=789 width=278) (actual
time=525.78..526.07 rows=276 loops=1)
        ->  Nested Loop  (cost=1.09..13317.68 rows=789 width=278) (actual
time=5.32..520.46 rows=276 loops=1)
              ->  Nested Loop  (cost=1.09..9749.11 rows=789 width=119)
(actual time=5.07..481.22 rows=276 loops=1)
                    ->  Hash Join  (cost=1.09..69.44 rows=9 width=84)
(actual time=1.24..3.89 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..68.20
rows=9 width=60) (actual time=0.08..2.17 rows=9 loops=1)
                          ->  Hash  (cost=1.07..1.07 rows=7 width=24)
(actual time=0.10..0.10 rows=0 loops=1)
                                ->  Seq Scan on distribution
(cost=0.00..1.07 rows=7 width=24) (actual time=0.04..0.07 rows=7 loops=1)
                    ->  Index Scan using idx_broadcast_channelregionid on
broadcast  (cost=0.00..1080.72 rows=88 width=35) (actual time=3.80..52.47
rows=31 loops=9)
              ->  Index Scan using episode_pkey on episode  (cost=0.00..4.51
rows=1 width=159) (actual time=0.09..0.11 rows=1 loops=276)
Total runtime: 598.78 msec