Re: ayuda con planeador

Listo, ya estan terminados los indices y paso las dos consultas.

la tabla sucursales_71 tiene casi 10'000.000 de datos y zcatxsuc
alrededor de 50'000.000

consulta 1. normalizada

QUERY PLAN
Limit (cost=279475.90..279475.98 rows=30 width=1289) (actual time=297833.744..2
97834.015 rows=30 loops=1)
-> Sort (cost=279475.90..279477.13 rows=490 width=1289) (actual time=297833.
735..297833.825 rows=30 loops=1)
Sort Key: sucursales_67.suc_norm_name_s
-> Nested Loop (cost=262.39..279454.01 rows=490 width=1289) (actual ti
me=67.691..297825.167 rows=519 loops=1)
-> Bitmap Heap Scan on zcatxsuc_67 (cost=262.39..95381.95 rows=4
5826 width=4) (actual time=64.582..140612.285 rows=45588 loops=1)
Recheck Cond: (cxs_cat_fk_n = 124911)
-> Bitmap Index Scan on cxs_cat_67_index (cost=0.00..262.3
9 rows=45826 width=0) (actual time=28.587..28.587 rows=45588 loops=1)
Index Cond: (cxs_cat_fk_n = 124911)
-> Index Scan using sucursales_67_pkey on sucursales_67 (cost=0.
00..4.00 rows=1 width=1289) (actual time=3.441..3.441 rows=0 loops=45588)
Index Cond: (sucursales_67.suc_id_n = "outer".cxs_suc_fk_n)
Filter: (suc_loc_fk_n = 59285)
Total runtime: 297834.853 ms

Consulta 2. Sin normalizar

QUERY PLAN
Limit (cost=2761.60..2761.67 rows=30 width=1289) (actual
time=118.971..119.241 rows=30 loops=1)
-> Sort (cost=2761.60..2762.80 rows=481 width=1289) (actual
time=118.963..119.053 rows=30 loops=1)
Sort Key: suc_norm_name_s
-> Bitmap Heap Scan on sucursales_67 (cost=845.94..2740.17
rows=481 width=1289) (actual time=112.599..114.837 rows=384 loops=1)
Recheck Cond: (((suc_cat1_fk_n = 124911) OR
(suc_cat2_fk_n = 124911) OR (suc_cat3_fk_n = 124911) OR (suc_cat4_fk_n
= 124911) OR (suc_cat5_fk_
n = 124911)) AND (suc_loc_fk_n = 59285))
-> BitmapAnd (cost=845.94..845.94 rows=482 width=0)
(actual time=112.187..112.187 rows=0 loops=1)
-> BitmapOr (cost=264.80..264.80 rows=45085
width=0) (actual time=22.979..22.979 rows=0 loops=1)
-> Bitmap Index Scan on suc_67_cat1_idx
(cost=0.00..51.47 rows=8706 width=0) (actual time=0.027..0.027 rows=0
loops=1)
Index Cond: (suc_cat1_fk_n = 124911)
-> Bitmap Index Scan on suc_67_cat2_idx
(cost=0.00..34.09 rows=5740 width=0) (actual time=0.017..0.017 rows=0
loops=1)
Index Cond: (suc_cat2_fk_n = 124911)
-> Bitmap Index Scan on suc_67_cat3_idx
(cost=0.00..23.01 rows=3717 width=0) (actual time=4.182..4.182
rows=8619 loops=1)
Index Cond: (suc_cat3_fk_n = 124911)
-> Bitmap Index Scan on suc_67_cat4_idx
(cost=0.00..69.92 rows=11978 width=0) (actual time=8.749..8.749
rows=13332 loops=1)
Index Cond: (suc_cat4_fk_n = 124911)
-> Bitmap Index Scan on suc_67_cat5_idx
(cost=0.00..86.31 rows=14945 width=0) (actual time=9.978..9.978
rows=12461 loops=1)
Index Cond: (suc_cat5_fk_n = 124911)
-> Bitmap Index Scan on suc_67_loc_fk_index
(cost=0.00..580.89 rows=101968 width=0) (actual time=69.979..69.979
rows=99445 loops=1)
Index Cond: (suc_loc_fk_n = 59285)
Total runtime: 120.001 ms