stubborn query confuses two different servers - Mailing list pgsql-performance

Dear Gurus,

Here is this strange query that can't find the optimum plan unless I disable
some scan modes or change the costs.

(A) is a 2x2.4GHz server with hw raid5 and v7.3.4 database. It chooses
hashjoin.
(B) is a 300MHz server with 7200rpm ide and v7.4.2 database. It chooses
seqscan.

If I disable hashjoin/seqscan+hashjoin+mergejoin, both choose index scan.
(A) goes from 1000ms to 55ms
(B) goes from 5000+ms to 300ms

If your expert eyes could catch something missing (an index, analyze or
something), I'd be greatly honoured :) Also, tips about which optimizer
costs may be too high or too low are highly appreciated.

As far as I fumbled with (B), disabling plans step by step got worse until
after disabled all tree. Reducing random_page_cost from 2 to 1.27 or lower
instantly activated the index scan, but I fear that it hurt most of our
other queries. The faster server did not respond to any changes, even with
rpc=1 and cpu_index_tuple_cost=0.0001, it chose hash join.

All that I discovered is that both servers fail to find the right index
(szlltlvl_ttl_szlltlvl) unless forced to.

In hope of an enlightening answer,
Yours,
G.
%----------------------- cut here -----------------------%
-- QUERY:
explain analyze -- 5000msec. rpc1.27-: 300
SELECT coalesce(szallitolevel,0) AS scope_kov_szallitolevel,
CASE 'raktáros' WHEN 'raktáros' THEN szallitolevel_bejovo_e(szallitolevel)
WHEN 'sofőr' THEN 1027=(SELECT coalesce(sofor,0) FROM szallitolevel WHERE
az=szallitolevel) ELSE true END
FROM
(SELECT l.az AS szallitolevel
FROM szallitolevel l, szallitolevel_tetele t
WHERE szallitas=1504 AND allapot NOT IN (6,7,8)
-- pakolandó tételekkel
  AND t.szallitolevel = l.az AND NOT t.archiv
-- ha archív van, de most nincs, legföljebb köv körben kibukik
  AND t.fajta IN (4,90,100)
GROUP BY t.szallitolevel, l.az
HAVING count(t.*)>0) t1
NATURAL FULL OUTER JOIN
(SELECT szallitolevel, az AS pakolas FROM pakolas WHERE szallitasba=1504 AND
sztornozott_pakolas IS NULL) t2
WHERE pakolas IS NULL ORDER BY 2 DESC LIMIT 1;
%----------------------- cut here -----------------------%
-- plan of (A), hashjoin --

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------------------------
 Limit  (cost=2795.58..2795.58 rows=1 width=12) (actual
time=1089.72..1089.72 rows=1 loops=1)
   ->  Sort  (cost=2795.58..2804.26 rows=3472 width=12) (actual
time=1089.72..1089.72 rows=2 loops=1)
         Sort Key: szallitolevel_bejovo_e(szallitolevel)
         ->  Merge Join  (cost=2569.48..2591.39 rows=3472 width=12) (actual
time=1086.72..1089.67 rows=2 loops=1)
               Merge Cond: ("outer".szallitolevel = "inner".szallitolevel)
               Filter: ("inner".az IS NULL)
               ->  Sort  (cost=1613.43..1614.15 rows=288 width=12) (actual
time=1054.21..1054.26 rows=80 loops=1)
                     Sort Key: t1.szallitolevel
                     ->  Subquery Scan t1  (cost=1572.82..1601.65 rows=288
width=12) (actual time=1050.72..1054.09 rows=80 loops=1)
                           ->  Aggregate  (cost=1572.82..1601.65 rows=288
width=12) (actual time=1050.70..1053.93 rows=80 loops=1)
                                 Filter: (count("*") > 0)
                                 ->  Group  (cost=1572.82..1594.44 rows=2883
width=12) (actual time=1050.64..1052.98 rows=824 loops=1)
                                       ->  Sort  (cost=1572.82..1580.03
rows=2883 width=12) (actual time=1050.63..1051.24 rows=824 loops=1)
                                             Sort Key: t.szallitolevel, l.az
                                             ->  Hash Join
(cost=531.09..1407.13 rows=2883 width=12) (actual time=8.13..1048.89
rows=824 loops=1)
                                                   Hash Cond:
("outer".szallitolevel = "inner".az)
                                                   ->  Index Scan using
szallitolevel_tetele_me on szallitolevel_tetele t  (cost=0.00..2.25
rows=167550 width=8) (actual time=0.18..871.77 rows=167888 loops=1)
                                                         Filter: ((NOT
archiv) AND ((fajta = 4) OR (fajta = 90) OR (fajta = 100)))
                                                   ->  Hash
(cost=530.06..530.06 rows=411 width=4) (actual time=7.92..7.92 rows=0
loops=1)
                                                         ->  Index Scan
using szlltlvl_szllts on szallitolevel l  (cost=0.00..530.06 rows=411
width=4) (actual time=0.04..7.81 rows=92 loops=1)
                                                               Index Cond:
(szallitas = 1504)
                                                               Filter:
((allapot <> 6) AND (allapot <> 7) AND (allapot <> 8))
               ->  Sort  (cost=956.05..964.73 rows=3472 width=8) (actual
time=27.80..30.24 rows=3456 loops=1)
                     Sort Key: pakolas.szallitolevel
                     ->  Index Scan using pakolas_szallitasba on pakolas
(cost=0.00..751.87 rows=3472 width=8) (actual time=0.10..21.68 rows=3456
loops=1)
                           Index Cond: (szallitasba = 1504)
                           Filter: (sztornozott_pakolas IS NULL)
 Total runtime: 1090.30 msec
(28 rows)

-- plan of (A), disabled hashjoin --

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------------------------
 Limit  (cost=10616.86..10616.86 rows=1 width=12) (actual time=45.04..45.04
rows=1 loops=1)
   ->  Sort  (cost=10616.86..10625.54 rows=3472 width=12) (actual
time=45.04..45.04 rows=2 loops=1)
         Sort Key: szallitolevel_bejovo_e(szallitolevel)
         ->  Merge Join  (cost=10390.77..10412.67 rows=3472 width=12)
(actual time=42.06..44.99 rows=2 loops=1)
               Merge Cond: ("outer".szallitolevel = "inner".szallitolevel)
               Filter: ("inner".az IS NULL)
               ->  Sort  (cost=9434.71..9435.43 rows=288 width=12) (actual
time=19.83..19.89 rows=80 loops=1)
                     Sort Key: t1.szallitolevel
                     ->  Subquery Scan t1  (cost=9394.10..9422.93 rows=288
width=12) (actual time=16.39..19.71 rows=80 loops=1)
                           ->  Aggregate  (cost=9394.10..9422.93 rows=288
width=12) (actual time=16.39..19.56 rows=80 loops=1)
                                 Filter: (count("*") > 0)
                                 ->  Group  (cost=9394.10..9415.72 rows=2883
width=12) (actual time=16.33..18.62 rows=824 loops=1)
                                       ->  Sort  (cost=9394.10..9401.31
rows=2883 width=12) (actual time=16.33..16.93 rows=824 loops=1)
                                             Sort Key: t.szallitolevel, l.az
                                             ->  Nested Loop
(cost=0.00..9228.41 rows=2883 width=12) (actual time=0.07..14.79 rows=824
loops=1)
                                                   ->  Index Scan using
szlltlvl_szllts on szallitolevel l  (cost=0.00..530.06 rows=411 width=4)
(actual time=0.04..7.97 rows=92 loops=1)
                                                         Index Cond:
(szallitas = 1504)
                                                         Filter: ((allapot
<> 6) AND (allapot <> 7) AND (allapot <> 8))
                                                   ->  Index Scan using
szlltlvl_ttl_szlltlvl on szallitolevel_tetele t  (cost=0.00..20.99 rows=12
width=8) (actual time=0.01..0.05 rows=9 loops=92)
                                                         Index Cond:
(t.szallitolevel = "outer".az)
                                                         Filter: ((NOT
archiv) AND ((fajta = 4) OR (fajta = 90) OR (fajta = 100)))
               ->  Sort  (cost=956.05..964.73 rows=3472 width=8) (actual tim
e=17.76..20.18 rows=3456 loops=1)
                     Sort Key: pakolas.szallitolevel
                     ->  Index Scan using pakolas_szallitasba on pakolas
(cost=0.00..751.87 rows=3472 width=8) (actual time=0.02..11.56 rows=3456
loops=1)
                           Index Cond: (szallitasba = 1504)
                           Filter: (sztornozott_pakolas IS NULL)
 Total runtime: 45.62 msec
(27 rows)

%----------------------- cut here -----------------------%
-- plan of (B), seqscan --

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------
 Limit  (cost=9132.84..9132.84 rows=1 width=8) (actual
time=3958.144..3958.144 rows=0 loops=1)
   ->  Sort  (cost=9132.84..9142.66 rows=3928 width=8) (actual
time=3958.132..3958.132 rows=0 loops=1)
         Sort Key: szallitolevel_bejovo_e(COALESCE(t1.szallitolevel,
pakolas.szallitolevel))
         ->  Merge Full Join  (cost=8834.40..8898.34 rows=3928 width=8)
(actual time=3958.099..3958.099 rows=0 loops=1)
               Merge Cond: ("outer".szallitolevel = "inner".szallitolevel)
               Filter: ("inner".az IS NULL)
               ->  Sort  (cost=7737.61..7743.49 rows=2352 width=4) (actual
time=3798.439..3798.594 rows=85 loops=1)
                     Sort Key: t1.szallitolevel
                     ->  Subquery Scan t1  (cost=7570.63..7605.91 rows=2352
width=4) (actual time=3796.553..3797.981 rows=85 loops=1)
                           ->  HashAggregate  (cost=7570.63..7582.39
rows=2352 width=12) (actual time=3796.535..3797.493 rows=85 loops=1)
                                 Filter: (count("*") > 0)
                                 ->  Hash Join  (cost=628.69..7552.99
rows=2352 width=12) (actual time=62.874..3785.188 rows=899 loops=1)
                                       Hash Cond: ("outer".szallitolevel =
"inner".az)
                                       ->  Seq Scan on szallitolevel_tetele
t  (cost=0.00..6062.06 rows=167743 width=8) (actual time=0.236..3072.882
rows=167637 loops=1)
                                             Filter: ((NOT archiv) AND
((fajta = 4) OR (fajta = 90) OR (fajta = 100)))
                                       ->  Hash  (cost=627.86..627.86
rows=335 width=4) (actual time=54.973..54.973 rows=0 loops=1)
                                             ->  Index Scan using
szlltlvl_szllts on szallitolevel l  (cost=0.00..627.86 rows=335 width=4)
(actual time=0.592..54.298 rows=91 loops=1)
                                                   Index Cond: (szallitas =
1504)
                                                   Filter: ((allapot <> 6)
AND (allapot <> 7) AND (allapot <> 8))
               ->  Sort  (cost=1096.79..1106.61 rows=3928 width=8) (actual
time=137.216..143.009 rows=3458 loops=1)
                     Sort Key: pakolas.szallitolevel
                     ->  Index Scan using pakolas_szallitasba on pakolas
(cost=0.00..862.30 rows=3928 width=8) (actual time=0.581..107.542 rows=3458
loops=1)
                           Index Cond: (szallitasba = 1504)
                           Filter: (sztornozott_pakolas IS NULL)
 Total runtime: 3971.008 ms
(25 rows)

-- plan of (B), disabled seqscan, hashjoin, mergejoin --

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------
 Limit  (cost=100012622.51..100012622.51 rows=1 width=8) (actual
time=295.102..295.102 rows=0 loops=1)
   ->  Sort  (cost=100012622.51..100012632.33 rows=3928 width=8) (actual
time=295.091..295.091 rows=0 loops=1)
         Sort Key: szallitolevel_bejovo_e(COALESCE(t1.szallitolevel,
pakolas.szallitolevel))
         ->  Merge Full Join  (cost=100012324.08..100012388.02 rows=3928
width=8) (actual time=295.057..295.057 rows=0 loops=1)
               Merge Cond: ("outer".szallitolevel = "inner".szallitolevel)
               Filter: ("inner".az IS NULL)
               ->  Sort  (cost=11227.29..11233.17 rows=2352 width=4) (actual
time=139.055..139.200 rows=85 loops=1)
                     Sort Key: t1.szallitolevel
                     ->  Subquery Scan t1  (cost=11060.30..11095.58
rows=2352 width=4) (actual time=137.166..138.588 rows=85 loops=1)
                           ->  HashAggregate  (cost=11060.30..11072.06
rows=2352 width=12) (actual time=137.150..138.106 rows=85 loops=1)
                                 Filter: (count("*") > 0)
                                 ->  Nested Loop  (cost=0.00..11042.66
rows=2352 width=12) (actual time=14.451..127.809 rows=899 loops=1)
                                       ->  Index Scan using szlltlvl_szllts
on szallitolevel l  (cost=0.00..627.86 rows=335 width=4) (actual
time=0.579..53.503 rows=91 loops=1)
                                             Index Cond: (szallitas = 1504)
                                             Filter: ((allapot <> 6) AND
(allapot <> 7) AND (allapot <> 8))
                                       ->  Index Scan using
szlltlvl_ttl_szlltlvl on szallitolevel_tetele t  (cost=0.00..30.91 rows=14
width=8) (actual time=0.232..0.714 rows=10 loops=91)
                                             Index Cond: (t.szallitolevel =
"outer".az)
                                             Filter: ((NOT archiv) AND
((fajta = 4) OR (fajta = 90) OR (fajta = 100)))
               ->  Sort  (cost=1096.79..1106.61 rows=3928 width=8) (actual
time=133.517..139.338 rows=3458 loops=1)
                     Sort Key: pakolas.szallitolevel
                     ->  Index Scan using pakolas_szallitasba on pakolas
(cost=0.00..862.30 rows=3928 width=8) (actual time=0.526..103.313 rows=3458
loops=1)
                           Index Cond: (szallitasba = 1504)
                           Filter: (sztornozott_pakolas IS NULL)
 Total runtime: 297.604 ms
(24 rows)


%----------------------- cut here -----------------------%