fake condition causes far better plan - Mailing list pgsql-performance

Dear Gurus,

System: Debian "Woody" 2.4.28
Version: PostgreSQL 7.4.8

I have a join which causes a better hash if I provide a "trivial" condition:
WHERE m.nap > '1900-01-01'::date
This is a date field with a minimum of '2005-06-21'. However, if I omit this
condition from the WHERE clause, I get a far worse plan. There's also
something not quite right in the cost tuning part of the config file, but I
*think* it shouldn't cause such a bad plan.

Explain analyze times:
With fake condition: 1104 msec
Without it: 11653 msec
Without, mergejoin disabled: 5776 msec

For full query and plans, see below. The operator "!=@" is the nonequity
operator extended so that it treats NULL as a one-element equivalence class,
thus never returning NULL. (NULL !=@ NULL is false, NULL !=@ "anything else"
is true)

1. What may be the cause that this "obvious" condition causes a far better
hash plan than the one without it, even while mergejoin is disabled?

2. What may be the cause that the planner favors mergejoin to hashjoin?
usually a sign of too high/too low random page cost, for example? I'm
willing to provide config options if it helps.

TIA,

--
G.

-------------- the query with fake condition (m.nap>=...) --------------
explain analyze
SELECT DISTINCT
   mv.az, mv.vonalkod, mv.idopont, mv.muszakhely as mvhely,
   mv.muszaknap as mvnap, mv.muszakkod as mvmkod,
   m.hely, m.nap, m.muszakkod as mkod, m.tol, m.ig
FROM muvelet_vonalkod mv
     left join olvaso_hely oh on (oh.olvaso_nev = mv.olvaso_nev
    and oh.tol <= mv.idopont and mv.idopont < oh.ig)
     left join muszak m on (oh.hely = m.hely
    and m.tol <= mv.idopont and mv.idopont < m.ig)
     , muvelet_vonalkod_ny ny
where mv.az = ny.muvelet_vonalkod
   and ny.idopont >= now()-1
   and m.nap >= '1900-01-01'::date
   and (mv.muszakhely!=@m.hely or mv.muszaknap!=@m.nap
    or mv.muszakkod!=@m.muszakkod);


-------------- best plan with fake condition --------------
  Unique  (cost=6484.22..6826.73 rows=11417 width=75) (actual
time=1103.870..1103.872 rows=1 loops=1)
    ->  Sort  (cost=6484.22..6512.76 rows=11417 width=75) (actual
time=1103.867..1103.868 rows=1 loops=1)
          Sort Key: mv.az, mv.vonalkod, mv.idopont, mv.muszakhely,
mv.muszaknap, mv.muszakkod, m.hely, m.nap, m.muszakkod, m.tol, m.ig
          ->  Hash Join  (cost=1169.78..5434.78 rows=11417 width=75) (actual
time=1075.836..1103.835 rows=1 loops=1)
                Hash Cond: ("outer".hely = "inner".hely)
                Join Filter: (("inner".tol <= "outer".idopont) AND
("outer".idopont < "inner".ig) AND (CASE WHEN (("outer".muszakhely IS NULL)
AND ("inner".hely IS NULL)) THEN false ELSE CASE WHEN (("outer".muszakhely
IS NULL) AND ("inner".hely IS NOT NULL)) THEN true ELSE CASE WHEN
(("outer".muszakhely IS NOT NULL) AND ("inner".hely IS NULL)) THEN true ELSE
("outer".muszakhely <> "inner".hely) END END END OR CASE WHEN
(("outer".muszaknap IS NULL) AND ("inner".nap IS NULL)) THEN false ELSE CASE
WHEN (("outer".muszaknap IS NULL) AND ("inner".nap IS NOT NULL)) THEN true
ELSE CASE WHEN (("outer".muszaknap IS NOT NULL) AND ("inner".nap IS NULL))
THEN true ELSE ("outer".muszaknap <> "inner".nap) END END END OR CASE WHEN
(("outer".muszakkod IS NULL) AND ("inner".muszakkod IS NULL)) THEN false
ELSE CASE WHEN (("outer".muszakkod IS NULL) AND ("inner".muszakkod IS NOT
NULL)) THEN true ELSE CASE WHEN (("outer".muszakkod IS NOT NULL) AND
("inner".muszakkod IS NULL)) THEN true ELSE ("outer".muszakkod <>
"inner".muszakkod) END END END))
                ->  Hash Join  (cost=1167.65..2860.48 rows=1370 width=51)
(actual time=533.035..741.211 rows=3943 loops=1)
                      Hash Cond: ("outer".muvelet_vonalkod = "inner".az)
                      ->  Index Scan using muvelet_vonalkod_ny_idopont on
muvelet_vonalkod_ny ny  (cost=0.00..1351.88 rows=24649 width=4) (actual
time=0.161..10.735 rows=3943 loops=1)
                            Index Cond: (idopont >= (now() -
('00:00:00'::interval + ('1 days'::text)::interval)))
                      ->  Hash  (cost=1124.61..1124.61 rows=3618 width=51)
(actual time=532.703..532.703 rows=0 loops=1)
                            ->  Nested Loop  (cost=0.00..1124.61 rows=3618
width=51) (actual time=0.209..443.765 rows=61418 loops=1)
                                  ->  Seq Scan on olvaso_hely oh
(cost=0.00..1.01 rows=1 width=28) (actual time=0.031..0.036 rows=1 loops=1)
                                  ->  Index Scan using muvelet_vonalkod_pk2
on muvelet_vonalkod mv  (cost=0.00..1060.30 rows=3617 width=55) (actual
time=0.162..244.158 rows=61418 loops=1)
                                        Index Cond:
((("outer".olvaso_nev)::text = (mv.olvaso_nev)::text) AND ("outer".tol <=
mv.idopont) AND (mv.idopont < "outer".ig))
                ->  Hash  (cost=1.94..1.94 rows=75 width=28) (actual
time=0.333..0.333 rows=0 loops=1)
                      ->  Seq Scan on muszak m  (cost=0.00..1.94 rows=75
width=28) (actual time=0.070..0.230 rows=73 loops=1)
                            Filter: (nap >= '2001-01-01'::date)
  Total runtime: 1104.244 ms
(19 rows)


-------------- mergejoin disabled, no fake condition --------------

  Unique  (cost=256601.12..262763.39 rows=205409 width=75) (actual
time=5776.476..5776.479 rows=1 loops=1)
    ->  Sort  (cost=256601.12..257114.64 rows=205409 width=75) (actual
time=5776.472..5776.472 rows=1 loops=1)
          Sort Key: mv.az, mv.vonalkod, mv.idopont, mv.muszakhely,
mv.muszaknap, mv.muszakkod, m.hely, m.nap, m.muszakkod, m.tol, m.ig
          ->  Hash Join  (cost=132547.25..228451.03 rows=205409 width=75)
(actual time=5733.661..5776.428 rows=1 loops=1)
                Hash Cond: ("outer".muvelet_vonalkod = "inner".az)
                ->  Index Scan using muvelet_vonalkod_ny_idopont on
muvelet_vonalkod_ny ny  (cost=0.00..1351.88 rows=24649 width=4) (actual
time=0.179..8.578 rows=3940 loops=1)
                      Index Cond: (idopont >= (now() - ('00:00:00'::interval
+ ('1 days'::text)::interval)))
                ->  Hash  (cost=124566.75..124566.75 rows=542600 width=75)
(actual time=5697.192..5697.192 rows=0 loops=1)
                      ->  Hash Left Join  (cost=2.95..124566.75 rows=542600
width=75) (actual time=33.430..5689.636 rows=484 loops=1)
                            Hash Cond: ("outer".hely = "inner".hely)
                            Join Filter: (("inner".tol <= "outer".idopont)
AND ("outer".idopont < "inner".ig))
                            Filter: (CASE WHEN (("outer".muszakhely IS NULL)
AND ("inner".hely IS NULL)) THEN false ELSE CASE WHEN (("outer".muszakhely
IS NULL) AND ("inner".hely IS NOT NULL)) THEN true ELSE CASE WHEN
(("outer".muszakhely IS NOT NULL) AND ("inner".hely IS NULL)) THEN true ELSE
("outer".muszakhely <> "inner".hely) END END END OR CASE WHEN
(("outer".muszaknap IS NULL) AND ("inner".nap IS NULL)) THEN false ELSE CASE
WHEN (("outer".muszaknap IS NULL) AND ("inner".nap IS NOT NULL)) THEN true
ELSE CASE WHEN (("outer".muszaknap IS NOT NULL) AND ("inner".nap IS NULL))
THEN true ELSE ("outer".muszaknap <> "inner".nap) END END END OR CASE WHEN
(("outer".muszakkod IS NULL) AND ("inner".muszakkod IS NULL)) THEN false
ELSE CASE WHEN (("outer".muszakkod IS NULL) AND ("inner".muszakkod IS NOT
NULL)) THEN true ELSE CASE WHEN (("outer".muszakkod IS NOT NULL) AND
("inner".muszakkod IS NULL)) THEN true ELSE ("outer".muszakkod <>
"inner".muszakkod) END END END)
                            ->  Hash Left Join  (cost=1.01..2317.03
rows=65112 width=51) (actual time=0.462..542.361 rows=61465 loops=1)
                                  Hash Cond: (("outer".olvaso_nev)::text =
("inner".olvaso_nev)::text)
                                  Join Filter: (("inner".tol <=
"outer".idopont) AND ("outer".idopont < "inner".ig))
                                  ->  Seq Scan on muvelet_vonalkod mv
(cost=0.00..1502.12 rows=65112 width=55) (actual time=0.028..123.649
rows=61465 loops=1)
                                  ->  Hash  (cost=1.01..1.01 rows=1
width=28) (actual time=0.045..0.045 rows=0 loops=1)
                                        ->  Seq Scan on olvaso_hely oh
(cost=0.00..1.01 rows=1 width=28) (actual time=0.031..0.033 rows=1 loops=1)
                            ->  Hash  (cost=1.75..1.75 rows=75 width=28)
(actual time=0.319..0.319 rows=0 loops=1)
                                  ->  Seq Scan on muszak m  (cost=0.00..1.75
rows=75 width=28) (actual time=0.067..0.215 rows=73 loops=1)
  Total runtime: 5776.778 ms
(21 rows)


-------------- mergejoin enabled, no fake condition --------------

  Unique  (cost=210234.71..216396.98 rows=205409 width=75) (actual
time=11652.868..11652.870 rows=1 loops=1)
    ->  Sort  (cost=210234.71..210748.24 rows=205409 width=75) (actual
time=11652.865..11652.865 rows=1 loops=1)
          Sort Key: mv.az, mv.vonalkod, mv.idopont, mv.muszakhely,
mv.muszaknap, mv.muszakkod, m.hely, m.nap, m.muszakkod, m.tol, m.ig
          ->  Merge Join  (cost=3152.69..182084.63 rows=205409 width=75)
(actual time=11408.433..11652.836 rows=1 loops=1)
                Merge Cond: ("outer".az = "inner".muvelet_vonalkod)
                ->  Nested Loop Left Join  (cost=2.76..174499.23 rows=542600
width=75) (actual time=1.506..11632.727 rows=484 loops=1)
                      Join Filter: (("outer".hely = "inner".hely) AND
("inner".tol <= "outer".idopont) AND ("outer".idopont < "inner".ig))
                      Filter: (CASE WHEN (("outer".muszakhely IS NULL) AND
("inner".hely IS NULL)) THEN false ELSE CASE WHEN (("outer".muszakhely IS
NULL) AND ("inner".hely IS NOT NULL)) THEN true ELSE CASE WHEN
(("outer".muszakhely IS NOT NULL) AND ("inner".hely IS NULL)) THEN true ELSE
("outer".muszakhely <> "inner".hely) END END END OR CASE WHEN
(("outer".muszaknap IS NULL) AND ("inner".nap IS NULL)) THEN false ELSE CASE
WHEN (("outer".muszaknap IS NULL) AND ("inner".nap IS NOT NULL)) THEN true
ELSE CASE WHEN (("outer".muszaknap IS NOT NULL) AND ("inner".nap IS NULL))
THEN true ELSE ("outer".muszaknap <> "inner".nap) END END END OR CASE WHEN
(("outer".muszakkod IS NULL) AND ("inner".muszakkod IS NULL)) THEN false
ELSE CASE WHEN (("outer".muszakkod IS NULL) AND ("inner".muszakkod IS NOT
NULL)) THEN true ELSE CASE WHEN (("outer".muszakkod IS NOT NULL) AND
("inner".muszakkod IS NULL)) THEN true ELSE ("outer".muszakkod <>
"inner".muszakkod) END END END)
                      ->  Nested Loop Left Join  (cost=1.01..3578.48
rows=65112 width=51) (actual time=0.140..757.392 rows=61461 loops=1)
                            Join Filter: ((("inner".olvaso_nev)::text =
("outer".olvaso_nev)::text) AND ("inner".tol <= "outer".idopont) AND
("outer".idopont < "inner".ig))
                            ->  Index Scan using muvelet_vonalkod_pkey on
muvelet_vonalkod mv  (cost=0.00..1786.89 rows=65112 width=55) (actual
time=0.103..144.516 rows=61461 loops=1)
                            ->  Materialize  (cost=1.01..1.02 rows=1
width=28) (actual time=0.001..0.002 rows=1 loops=61461)
                                  ->  Seq Scan on olvaso_hely oh
(cost=0.00..1.01 rows=1 width=28) (actual time=0.005..0.007 rows=1 loops=1)
                      ->  Materialize  (cost=1.75..2.50 rows=75 width=28)
(actual time=0.001..0.054 rows=73 loops=61461)
                            ->  Seq Scan on muszak m  (cost=0.00..1.75
rows=75 width=28) (actual time=0.012..0.179 rows=73 loops=1)
                ->  Sort  (cost=3149.93..3211.55 rows=24649 width=4) (actual
time=15.420..17.108 rows=2356 loops=1)
                      Sort Key: ny.muvelet_vonalkod
                      ->  Index Scan using muvelet_vonalkod_ny_idopont on
muvelet_vonalkod_ny ny  (cost=0.00..1351.88 rows=24649 width=4) (actual
time=0.048..9.502 rows=3942 loops=1)
                            Index Cond: (idopont >= (now() -
('00:00:00'::interval + ('1 days'::text)::interval)))
  Total runtime: 11653.429 ms
(20 rows)