Problem z wydajnoscia podzapytania

Witam wszystkich, to jest moj pierwszy post na tym forum.

Problem polega na tym ze dodanie dodatkowego warunku w podzapytaniu zabija wydajnosc.
Sa trzy tabele:
select count(*) from booklet_matrix ;
15086

select count(*) from booklet ;
  2846

select count(*) from booklet_elements ;
  6729

Tabelka booklet_matrix to zlaczenie obu tabel:
\d booklet_matrix;
Table "public.booklet_matrix"
Column |  Type   | Modifiers
--------+---------+-----------
id_b   | integer |
id_e   | integer |
name   | text    |
Indexes:
    "booklet_matrix_idb" btree (id_b)
    "booklet_matrix_ide" btree (id_e)
Foreign-key constraints:
    "booklet_matrix_id_b_fkey" FOREIGN KEY (id_b) REFERENCES booklet(id)
    "booklet_matrix_id_e_fkey" FOREIGN KEY (id_e) REFERENCES booklet_elements(id)

Tabela booklet:
\d booklet;
                            Table "public.booklet"
   Column    |  Type   |                      Modifiers                       
-------------+---------+------------------------------------------------------
id          | integer | not null default nextval('booklet_id_seq'::regclass)
id_f        | integer |
code        | text    |
description | text    |
issue       | text    |
status      | integer |
job         | integer |
old_issue   | integer | default 0
snapshot    | boolean | default false
n_status    | integer |
type        | integer | default 1
Indexes:
    "booklet_pkey" PRIMARY KEY, btree (id)
    "booklet_family_idx" btree (id_f)
    "booklet_status_key" btree (status)
Triggers:
    issue AFTER UPDATE ON booklet FOR EACH ROW EXECUTE PROCEDURE old_issue()

Tabela booklet_elements jest malo istotna, nie sprawia problemow (narazie).

Laczenie
Lacze wszystkie trzy tabele, szukajac rekordow ktore spelniaja warunek w jednej lub drugiej, zwraca mi to czego szukalem, ale pojawia sie problem z wydajnoscia

Zapytanie szybkie:
explain ANALYZE select id_f
from
booklet_matrix bm
RIGHT JOIN (select * from booklet_elements be where be.status in (1)) be on be.id = bm.id_e
RIGHT JOIN (select * from booklet b where b.type=1 and b.status in (1,2)) b on b.id = bm.id_b;
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join  (cost=336.10..432.68 rows=106 width=4) (actual time=0.411..0.567 rows=56 loops=1)
   Hash Cond: (b.id = bm.id_b)
   ->  Bitmap Heap Scan on booklet b  (cost=9.32..105.49 rows=106 width=8) (actual time=0.354..0.424 rows=56 loops=1)
         Recheck Cond: (status = ANY ('{1,2}'::integer[]))
         Filter: ("type" = 1)
         ->  Bitmap Index Scan on booklet_status_key  (cost=0.00..9.30 rows=106 width=0) (actual time=0.155..0.155 rows=809 loops=1)
               Index Cond: (status = ANY ('{1,2}'::integer[]))
   ->  Hash  (cost=325.58..325.58 rows=96 width=4) (actual time=0.045..0.045 rows=0 loops=1)
         ->  Hash Join  (cost=11.18..325.58 rows=96 width=4) (actual time=0.044..0.044 rows=0 loops=1)
               Hash Cond: (bm.id_e = be.id)
               ->  Seq Scan on booklet_matrix bm  (cost=0.00..256.86 rows=15086 width=8) (actual time=0.014..0.014 rows=1 loops=1)
               ->  Hash  (cost=10.65..10.65 rows=43 width=4) (actual time=0.024..0.024 rows=0 loops=1)
                     ->  Index Scan using booklet_elements_status on booklet_elements be  (cost=0.00..10.65 rows=43 width=4) (actual time=0.023..0.023 rows=0 loops=1)
                           Index Cond: (status = 1)
Total runtime: 0.662 ms
(15 rows)

Dodanie jednego warunku w podzapytaniu przeszkadza:
pcss=# explain ANALYZE select id_f
from
booklet_matrix bm
RIGHT JOIN (select * from booklet_elements be where be.status in (1)) be on be.id = bm.id_e
RIGHT JOIN (select * from booklet b where b.id_f=229 and b.status in (1,2)) b on b.id = bm.id_b;
                                                                           QUERY PLAN                                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join  (cost=20.48..432.24 rows=1 width=4) (actual time=0.449..996.961 rows=42 loops=1)
   Join Filter: (b.id = bm.id_b)
   ->  Bitmap Heap Scan on booklet b  (cost=9.30..105.46 rows=1 width=8) (actual time=0.395..0.509 rows=42 loops=1)
         Recheck Cond: (status = ANY ('{1,2}'::integer[]))
         Filter: (id_f = 229)
         ->  Bitmap Index Scan on booklet_status_key  (cost=0.00..9.30 rows=106 width=0) (actual time=0.159..0.159 rows=809 loops=1)
               Index Cond: (status = ANY ('{1,2}'::integer[]))
   ->  Hash Join  (cost=11.18..325.58 rows=96 width=4) (actual time=23.721..23.721 rows=0 loops=42)
         Hash Cond: (bm.id_e = be.id)
         ->  Seq Scan on booklet_matrix bm  (cost=0.00..256.86 rows=15086 width=8) (actual time=0.006..11.845 rows=14727 loops=42)
         ->  Hash  (cost=10.65..10.65 rows=43 width=4) (actual time=0.025..0.025 rows=0 loops=1)
               ->  Index Scan using booklet_elements_status on booklet_elements be  (cost=0.00..10.65 rows=43 width=4) (actual time=0.023..0.023 rows=0 loops=1)
                     Index Cond: (status = 1)
Total runtime: 997.060 ms
(14 rows)

Co poradzic?

Ostatnio edytowany przez dzik (2009-01-13 16:10:48)