Re: query reboot pgsql 9.5.1 - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: query reboot pgsql 9.5.1 |
Date | |
Msg-id | 56D9F336.4060905@aklaver.com Whole thread Raw |
In response to | query reboot pgsql 9.5.1 (Felipe de Jesús Molina Bravo <fjmolinabravo@gmail.com>) |
Responses |
Re: query reboot pgsql 9.5.1
(Felipe de Jesús Molina Bravo <fjmolinabravo@gmail.com>)
|
List | pgsql-general |
On 03/04/2016 12:09 PM, Felipe de Jesús Molina Bravo wrote: > 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)) The above are exactly the same, so if they are indeed from the different versions I do not see an issue. The question to ask here is whether the above are actually from the different Postgres instances? > > 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 So is each Postgres instance running in a separate container and if so are they set up the same? > > > 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! -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: