Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
Can you post the SELECT statement as well? –  a_horse_with_no_name Apr 6 '12 at 9:30
    
I could, sorry. –  dezso Apr 6 '12 at 9:38
    
Try replacing all those needless casts e.g. current_date instead of 'now'::text::date (although I doubt that it will make a big difference). But the index scan does indeed look very strange –  a_horse_with_no_name Apr 6 '12 at 9:46
    
Those casts are not my inventions, this is how Postgresql (or EMS Sql Manager?) translates CURRENT_DATE. I hate it nonetheless. –  dezso Apr 6 '12 at 9:51
add comment

1 Answer

up vote 1 down vote accepted

Well, the solution was rephrasing NOT EXISTS to NOT IN. So the index scan and the whole query run quite fast as one would expect.

share|improve this answer
    
Thanks for sharing the answer. It's interesting though, because most people believe NOT EXISTS is generally faster than NOT IN –  a_horse_with_no_name Apr 12 '12 at 8:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.