Re: Strange behaviour with a query

Explain Analyze from a second database (it's a replica). With original
database and index in art_id table it never ends, like the query...:
That is, in original database only works if index rel_dis_can_fk is
deleted, but in replicated one it works without problems. ¿¿¿??? Same
hardware, same operating system, same postgres database version....

"Limit (cost=2312.74..2312.74 rows=1 width=40) (actual
time=96.906..96.906 rows=0 loops=1)"
" -> Sort (cost=2312.74..2312.74 rows=1 width=40) (actual
time=96.902..96.902 rows=0 loops=1)"
" Sort Key: dis.dis_horas, art.art_fecha_up"
" -> Nested Loop (cost=47.61..2312.73 rows=1 width=40) (actual
time=96.885..96.885 rows=0 loops=1)"
" Join Filter: (dis.dis_id = art.dis_id)"
" -> Nested Loop (cost=47.61..2311.46 rows=1 width=40)
(actual time=96.883..96.883 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..2259.82 rows=1
width=52) (actual time=96.882..96.882 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..2259.32 rows=1
width=64) (actual time=0.134..72.967 rows=1194 loops=1)"
" -> Index Scan using ix_pre_id on
articulo art (cost=0.00..2233.39 rows=3 width=40) (actual
time=0.102..51.898 rows=1175 loops=1)"
" Index Cond: (pre_id =
5::numeric)"
" Filter: ((art_aprobado =
1::numeric) AND ((art_show_anyway = 1::numeric) OR
(art_stock_almacen_central >= 1::numeric) OR (art_stock_local >=
1::numeric) OR (art_stock_local_2 >= 1::numeric) OR (art_stock >=
1::numeric)) AND (art_descatalogado <> 1::numeric) AND (art_buyable =
1::numeric) AND (nvl(art_precio_fnac_web, 0::numeric) > 0::numeric) AND
(art_pmp IS NOT NULL) AND (tip_id = 1::numeric))"
" -> Index Scan using
rel_participacion_articulo_fk on participacion par (cost=0.00..8.63
rows=1 width=24) (actual time=0.013..0.014 rows=1 loops=1175)"
" Index Cond: (par.art_id =
art.art_id)"
" Filter: (rol_id =
100004::numeric)"
" -> Index Scan using ix_ent_id on ente ent
(cost=0.00..0.49 rows=1 width=12) (actual time=0.018..0.018 rows=0
loops=1194)"
" Index Cond: (par.ent_id = ent.ent_id)"
" Filter:
((ent_nombre_search_string)::text % '4 NON BLONDES'::text)"
" -> Bitmap Heap Scan on cancion c
(cost=47.61..51.62 rows=1 width=12) (never executed)"
" Recheck Cond: ((c.art_id = art.art_id) AND
((c.can_nombre_search_string)::text % 'WHATS UP'::text))"
" -> BitmapAnd (cost=47.61..47.61 rows=1
width=0) (never executed)"
" -> Bitmap Index Scan on rel_dis_can_fk
(cost=0.00..5.60 rows=169 width=0) (never executed)"
" Index Cond: (c.art_id =
art.art_id)"
" -> Bitmap Index Scan on
cancion_nombre_search_string_trgm (cost=0.00..41.59 rows=666 width=0)
(never executed)"
" Index Cond:
((can_nombre_search_string)::text % 'WHATS UP'::text)"
" -> Seq Scan on disponibilidad dis (cost=0.00..1.12
rows=12 width=20) (never executed)"
"Total runtime: 97.119 ms"

-----Original Message-----
From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Iñigo Martinez Lasala <imartinez(at)vectorsf(dot)com>
Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)org>, Jaume Sabater
<jsabater(at)gmail(dot)com>, Sergio Chavarria <sergio(dot)chavarria(at)gmail(dot)com>
Subject: Re: [ADMIN] Strange behaviour with a query
Date: Fri, 17 Apr 2009 04:17:40 -0600

On Fri, Apr 17, 2009 at 3:00 AM, Iñigo Martinez Lasala
<imartinez(at)vectorsf(dot)com> wrote:
> Hi everybody again.
>
> Deleting rel_dis_can_fk index has solved the problem! But.... why??

Hard to say without explain analyze output.