Re: Ayuda con estadisticas

Este es el explain analyze del query, voy a reducir los shared buffers y ver
como se comporta, aunque no creo que sea eso pues he reiniciado postgres y
sigue demorandose poco. Voy a hacer la prueba con el cache de los discos,
aunque si es esto ultimo me preocupa.

Adiciono al final del query plan, la descripcion de las tablas con sus
indices.

La base de datos es 7.4.7, tengo que actualizarla, pero por ahora solo la
pasare a 7.4.12, sistema operativo Debian Sarge, 4G RAM, procesador dual
Xeon 3.06 Ghz

QUERY PLAN

Limit (cost=6219.68..6219.83 rows=60 width=218) (actual
time=782.821..783.376 rows=60 loops=1)

-> Sort (cost=6219.68..6222.32 rows=1058 width=218) (actual
time=782.807..782.995 rows=60 loops=1)

Sort Key: sucursales_136.suc_norm_name_s

-> Merge Join (cost=6028.63..6166.53 rows=1058 width=218)
(actual time=513.818..656.659 rows=10027 loops=1)

Merge Cond: ("outer".loc_id_n = "inner".suc_loc_fk_n)

-> Index Scan using location_pkey on "location"
(cost=0.00..4623.90 rows=135046 width=17) (actual time=0.021..10.532
rows=2474 loops=1)

-> Sort (cost=6028.63..6031.28 rows=1058 width=205)
(actual time=508.488..539.729 rows=10027 loops=1)

Sort Key: sucursales_136.suc_loc_fk_n

-> Nested Loop (cost=0.00..5975.48 rows=1058
width=205) (actual time=0.340..452.104 rows=10027 loops=1)

-> Index Scan using cxs_cat_136_index on
zcatxsuc_136 (cost=0.00..1832.58 rows=1058 width=4) (actual
time=0.211..77.179 rows=10027 loops=1)

Index Cond: (cxs_cat_fk_n = 809)

-> Index Scan using sucursales_136_pkey on
sucursales_136 (cost=0.00..3.90 rows=1 width=205) (actual
time=0.017..0.021 rows=1 loops=10027)

Index Cond: (sucursales_136.suc_id_n =
"outer".cxs_suc_fk_n)

aquii_new_scheme=# \d sucursales_136
Table "public.sucursales_136"
Column | Type |
Modifiers
-----------------+------------------------+----------------------------------------------------------------------
suc_id_n | integer | not null default nextval('
public.sucursales_136_suc_id_n_seq'::text)
suc_loc_fk_n | integer | not null
suc_name_s | character varying(500) | not null
suc_tel1_s | character varying(20) |
suc_tel2_s | character varying(20) |
suc_fax_s | character varying(20) |
suc_email_s | character varying(100) |
suc_url_s | character varying(100) |
suc_address_s | character varying(100) |
suc_logo_s | character varying(100) |
suc_verified_b | boolean | default false
suc_zip_s | character varying(30) |
suc_norm_name_s | character varying(500) | not null
suc_legal_s | character varying(100) |
suc_door_s | character varying(100) |
suc_suite_s | character varying(100) |
suc_areacode_s | character varying(100) |
Indexes:
"sucursales_136_pkey" primary key, btree (suc_id_n)
"suc_136_loc_fk_index" btree (suc_loc_fk_n)
"suc_136_name_index" btree (suc_name_s varchar_pattern_ops)
"suc_136_name_lower_index" btree (lower((suc_name_s)::text)
varchar_pattern_ops)
"suc_136_norm_index" btree (suc_norm_name_s varchar_pattern_ops)
"suc_name_regular_index_136" btree (suc_name_s)
"suc_norm_regular_index_136" btree (suc_norm_name_s)
Foreign-key constraints:
"suc_136_suc" FOREIGN KEY (suc_id_n, suc_loc_fk_n) REFERENCES
sucursales(suc_id_n, suc_loc_fk_n)
"suc_136_loc" FOREIGN KEY (suc_loc_fk_n) REFERENCES "location"(loc_id_n)

aquii_new_scheme=# \d location
Table "public.location"
Column | Type |
Modifiers
------------------+------------------------+----------------------------------------------------------------
loc_id_n | integer | not null default nextval('
public.location_loc_id_n_seq'::text)
loc_name_fk_n | integer |
loc_desc_fk_n | integer |
loc_fk_n | integer | not null
loc_graf1_s | character varying(30) |
loc_graf2_s | character varying(30) |
loc_graf3_s | character varying(30) |
loc_maps_s | character varying(500) |
loc_lot_fk_n | integer | not null
loc_name_s | character varying(150) |
loc_pop_n | integer |
loc_coord_s | character varying(30) |
loc_norm_name_s | character varying(150) |
loc_short_name_s | character varying(5) |
Indexes:
"location_pkey" primary key, btree (loc_id_n)
"loc_loc_fk_index" btree (loc_fk_n)
"loc_name_fk_index" btree (loc_name_fk_n)
"loc_name_index" btree (loc_name_s)
"loc_norm_index" btree (loc_norm_name_s)
Foreign-key constraints:
"loc_mes" FOREIGN KEY (loc_name_fk_n) REFERENCES messages(mes_id_n)
"loc_lot" FOREIGN KEY (loc_lot_fk_n) REFERENCES locationtype(lot_id_n)
"loc_loc" FOREIGN KEY (loc_fk_n) REFERENCES "location"(loc_id_n)

aquii_new_scheme=# \d zcatxsuc_136
Table "public.zcatxsuc_136"
Column | Type | Modifiers
--------------+---------+-----------
cxs_suc_fk_n | integer | not null
cxs_cat_fk_n | integer | not null
Indexes:
"zcatxsuc_136_pkey" primary key, btree (cxs_suc_fk_n, cxs_cat_fk_n)
"cxs_cat_136_index" btree (cxs_cat_fk_n)
"cxs_suc_136_index" btree (cxs_suc_fk_n)
Foreign-key constraints:
"cxs_136_suc_136" FOREIGN KEY (cxs_suc_fk_n) REFERENCES
sucursales_136(suc_id_n)
"cxs_136_cat" FOREIGN KEY (cxs_cat_fk_n) REFERENCES category(cat_id_n)