Re: Strange behaviour with a query - Mailing list pgsql-admin

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.marlowe@gmail.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>, Jaume Sabater <jsabater@gmail.com>, Sergio Chavarria <sergio.chavarria@gmail.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@vectorsf.com> wrote:
> Hi everybody again.
>
> Deleting rel_dis_can_fk index has solved the problem! But.... why??

Hard to say without explain analyze output.