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 <[email protected]> To: Iñigo Martinez Lasala <[email protected]> Cc: pgsql-admin <[email protected]>, Jaume Sabater <[email protected]>, Sergio Chavarria <[email protected]> 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 <[email protected]> wrote: > Hi everybody again. > > Deleting rel_dis_can_fk index has solved the problem! But.... why?? Hard to say without explain analyze output.
