Postgresql – slow index scan on postgresql 9.1

I have a view which is amazingly slow 🙂 It joins two tables only, having a few simple WHERE conditions and one in a form of a subquery (see SubPlan1 below). The simplest condition is checked as an index scan, which is somehow terribly slow:

QUERY PLAN
Result  (cost=19.41..570789.79 rows=18 width=4) (actual time=18724.919..18724.919 rows=0 loops=1)
  One-Time Filter: ((hashed SubPlan 2) AND (('now'::text)::time with time zone >= ($2)::time with time zone) AND (('now'::text)::time with time zone <= ($3)::time with time zone))
  InitPlan 3 (returns $2)
    ->  Function Scan on f_konstansok_select f  (cost=0.26..0.27 rows=1 width=146) (actual time=0.147..0.147 rows=1 loops=1)
  InitPlan 4 (returns $3)
    ->  Function Scan on f_konstansok_select f  (cost=0.26..0.27 rows=1 width=146) (actual time=0.103..0.103 rows=1 loops=1)
  ->  Nested Loop  (cost=18.87..570789.25 rows=18 width=4) (actual time=18724.357..18724.357 rows=0 loops=1)
        Join Filter: (((ml.idopont_tervezett)::timestamp without time zone < now()) OR ((ml.idopont_tervezett IS NULL) AND ((ml.idopont + (((ms.hatarido)::text || ' minutes'::text))::interval) < now())))
        ->  Nested Loop Semi Join  (cost=0.00..570746.76 rows=42 width=45) (actual time=6.393..18711.516 rows=61 loops=1)
              ->  Index Scan using idx_munkalap_lepes_aktualis on munkalap_lepes ml  (cost=0.00..570080.42 rows=1634 width=45) (actual time=6.379..18678.306 rows=3256 loops=1)
                    Index Cond: (aktualis = true)
                    Filter: (NOT (SubPlan 1))
                    SubPlan 1
                      ->  Nested Loop  (cost=681.75..2041.93 rows=1 width=0) (actual time=5.712..5.712 rows=0 loops=3268)
                            ->  Hash Join  (cost=681.75..2033.65 rows=1 width=4) (actual time=5.711..5.711 rows=0 loops=3268)
                                  Hash Cond: (ls.munkalap_statusz_figyelmeztetes_id = msf.munkalap_statusz_figyelmeztetes_id)
                                  Join Filter: (ls.idopont > (now() - (((msf.figyelmeztetes_periodus)::text || ' minutes'::text))::interval))
                                  ->  Bitmap Heap Scan on lepes_sorrend ls  (cost=680.26..2032.12 rows=1 width=16) (actual time=5.706..5.707 rows=0 loops=3268)
                                        Recheck Cond: (munkalap_statusz_figyelmeztetes_id IS NOT NULL)
                                        Filter: (munkalap_lepes_id = ml.munkalap_lepes_id)
                                        ->  Bitmap Index Scan on lepes_sorrend_idx2  (cost=0.00..680.26 rows=36389 width=0) (actual time=1.814..1.814 rows=36337 loops=3268)
                                              Index Cond: (munkalap_statusz_figyelmeztetes_id IS NOT NULL)
                                  ->  Hash  (cost=1.22..1.22 rows=22 width=8) (actual time=0.009..0.009 rows=22 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                        ->  Seq Scan on munkalap_statusz_figyelmeztetes msf  (cost=0.00..1.22 rows=22 width=8) (actual time=0.003..0.004 rows=22 loops=1)
                            ->  Index Scan using pk_munkalap_lepes on munkalap_lepes ml2  (cost=0.00..8.27 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=12)
                                  Index Cond: (munkalap_lepes_id = ml.munkalap_lepes_id)
                                  Filter: aktualis
              ->  Index Scan using pk_munkalap on munkalap m  (cost=0.00..0.41 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=3256)
                    Index Cond: (munkalap_id = ml.munkalap_id)
                    Filter: ((NOT lezarva) AND (NOT torolve))
        ->  Index Scan using pk_munkalap_statusz on munkalap_statusz ms  (cost=0.00..0.27 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=61)
              Index Cond: (munkalap_statusz_id = ml.munkalap_statusz_id)
  SubPlan 2
    ->  Seq Scan on munkanapok  (cost=0.00..16.27 rows=1027 width=4) (actual time=0.008..0.113 rows=1027 loops=1)
Total runtime: 18725.001 ms

The index definition is:

CREATE INDEX idx_munkalap_lepes_aktualis ON hibabejelentes.munkalap_lepes
  USING btree (aktualis)
  WHERE aktualis;

'aktualis' is a boolean flag, the name meaning 'current' (for those who wondered 🙂

After playing it for a while I still have no idea how to make it fast… omitting the other conditions makes the query run reasonably fast:

QUERY PLAN
Hash Join  (cost=2.01..428.98 rows=3271 width=4) (actual time=0.449..3.780 rows=3268 loops=1)
  Hash Cond: (ml.munkalap_statusz_id = ms.munkalap_statusz_id)
  ->  Index Scan using idx_munkalap_lepes_aktualis on munkalap_lepes ml  (cost=0.00..381.99 rows=3271 width=8) (actual time=0.410..2.846 rows=3268 loops=1)
        Index Cond: (aktualis = true)
  ->  Hash  (cost=1.45..1.45 rows=45 width=4) (actual time=0.025..0.025 rows=45 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 2kB
        ->  Seq Scan on munkalap_statusz ms  (cost=0.00..1.45 rows=45 width=4) (actual time=0.007..0.012 rows=45 loops=1)
Total runtime: 3.971 ms

All tables are autovacuumed.

Thanks in advance for any idea.

EDIT: SELECT statement included

CREATE OR REPLACE VIEW hibabejelentes.v_munkalap_figyelmeztetes_kuldes(
    munkalap_id)
AS
  SELECT ml.munkalap_id
  FROM munkalap_lepes ml
       JOIN munkalap_statusz ms USING (munkalap_statusz_id)
  WHERE ml.aktualis AND
        (ml.idopont_tervezett::timestamp without time zone < now() OR
        ml.idopont_tervezett IS NULL AND
        (ml.idopont +((ms.hatarido || ' minutes' ::text) ::interval)) < now()) AND

        NOT (EXISTS (
                      SELECT ls.munkalap_statusz_figyelmeztetes_id
                      FROM lepes_sorrend ls
                           JOIN munkalap_lepes ml2 USING (munkalap_lepes_id)
                           JOIN munkalap_statusz_figyelmeztetes msf USING (
                           munkalap_statusz_figyelmeztetes_id)
                      WHERE ml2.aktualis AND
                            ml2.munkalap_lepes_id = ml.munkalap_lepes_id AND
                            ls.munkalap_statusz_figyelmeztetes_id IS NOT NULL AND
                            ls.idopont >(now() -((msf.figyelmeztetes_periodus ||
                             ' minutes' ::text) ::interval))
        )) AND
        ('now' ::text::date IN (
                                 SELECT munkanapok.datum
                                 FROM munkanapok
        )) AND
        'now'        ::text::time with time zone >=((
                                                      SELECT f.ertek::time
                                                       without time zone AS
                                                        ertek
                                                      FROM f_konstansok_select(
                                                      'MUNKAIDO_ELEJE' ::text,
                                                       'now' ::text::date,
                                                        NULL::integer) f(
                                                        konstans_id,
                                                         konstans_nev, datum,
                                                          ertek, torolve,
                                                           bolthalozat_id)
        )) ::time with time zone AND
        'now'        ::text::time with time zone <=((
                                                      SELECT f.ertek::time
                                                       without time zone AS
                                                        ertek
                                                      FROM f_konstansok_select(
                                                      'MUNKAIDO_VEGE' ::text,
                                                       'now' ::text::date,
                                                        NULL::integer) f(
                                                        konstans_id,
                                                         konstans_nev, datum,
                                                          ertek, torolve,
                                                           bolthalozat_id)
        )) ::time with time zone;