Hi!!! I try to explain my problem...sorry for my english :( In pgsql 9.5.1 I have a two tables with the next structure: 1. Tabla unlogged «public._gc_cat» Columna | Tipo | Modificadores -----------------+--------------+--------------- idppicat | integer | idprodxintegrar | integer | tipo | character(1) | valor | numeric | estado | character(1) | idsll | text | idsfte | text | arama | text[] | ne_arama | integer | rama | text | rvar | text | nodec | integer | Índices: "_gc_cat_arama" btree (ne_arama) "_gc_cat_arama_gin" gin (arama) 2. Tabla unlogged «public._gc_tb» Columna | Tipo | Modificadores ----------+---------+--------------- idb2 | integer | idc1 | integer | rama | text | arama | text[] | ne_arama | integer | Índices: "_gc_tb_arama" btree (ne_arama) "_gc_tb_arama_gin" gin (arama) "_gc_tb_idb2idc1" btree (idb2, idc1) the tabla _gc_cat have 91932 records an _gc_tb have 120130 records; when i run the next query: SELECT idprodxintegrar FROM _gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama < @ b.arama ) psql send the next message (after three minutes aprox.): Terminado (killed) and i have to reboot my "guest server". Now i execute the same in pgsql 9.4.5 and all is fine!!! The EXPLAINs are: - pgsql 9.5.1: Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=66) -> Bitmap Heap Scan on _gc_cat b (cost=0.03..4.06 rows=2 width=70) Recheck Cond: ((arama <@ a.arama) AND (a.arama <@ arama)) -> Bitmap Index Scan on _gc_cat_arama_gin (cost=0.00..0.03 rows=2 width=0) Index Cond: ((arama <@ a.arama) AND (a.arama <@ arama)) - pgsql 9.4.5: Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=66) -> Bitmap Heap Scan on _gc_cat b (cost=0.03..4.06 rows=2 width=70) Recheck Cond: ((arama <@ a.arama) AND (a.arama <@ arama)) -> Bitmap Index Scan on _gc_cat_arama_gin (cost=0.00..0.03 rows=2 width=0) Index Cond: ((arama <@ a.arama) AND (a.arama <@ arama)) If i change the query as: SELECT idprodxintegrar FROM _gc_tb a LEFT join _gc_cat b on ( a.ne_arama = b.ne_arama and a.arama <@ b.arama ) In pgsql 9.5.1 finished after 450708.112 ms In pgsql 9.4.5 finished after 17996.756 ms (very fast!!!) The EXPLAINs are: - pgsql 9.5.1 Nested Loop Left Join (cost=3.49..1915550.34 rows=41825277 width=4) -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=70) -> Bitmap Heap Scan on _gc_cat b (cost=3.49..14.39 rows=153 width=74) Recheck Cond: (a.arama <@ arama) Filter: (a.ne_arama = ne_arama) -> Bitmap Index Scan on _gc_cat_arama_gin (cost=0.00..3.45 rows=460 width=0) Index Cond: (a.arama <@ arama) - pgsql 9.4.5 Nested Loop Left Join (cost=3.48..1868759.71 rows=42284738 width=4) -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=70) -> Bitmap Heap Scan on _gc_cat b (cost=3.48..14.38 rows=115 width=74) Recheck Cond: (a.arama <@ arama) Filter: (a.ne_arama = ne_arama) -> Bitmap Index Scan on _gc_cat_arama_gin (cost=0.00..3.45 rows=460 width=0) Index Cond: (a.arama <@ arama) The shared_buffers and work_mem are the same in both versions of pgsql (128MB and 4MB) I am doing this test in a laptop with the next characteristics: - hp probook with 8 Gb ram. SATA disk, AMD A8-5550M - OS Linux (fedora 23) - lxc containers I am sharing the dumper's database are in the next links: http://sipre.inegi.org.mx/pgsql/dump_pgsql_9_4_5.dump http://sipre.inegi.org.mx/pgsql/dump_pgsql_9_5_1.dump similar post is found in: http://www.postgresql.org/message-id/CALrs2KPMowV6juLdOkMRq_P3MA5VkUmhdM4Q1OD0vCf2qimFfA@mail.gmail. com thanks in advance! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
