From: | Felipe de Jesús Molina Bravo <fjmolinabravo(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | query reboot pgsql 9.5.1 |
Date: | 2016-03-04 20:09:37 |
Message-ID: | CALrs2KMK9rjuL-6_ubes7ca62hXcz6M-vLF2vX+n9Gtihj0HsQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-03-04 20:42:30 | Re: query reboot pgsql 9.5.1 |
Previous Message | MOLINA BRAVO FELIPE DE JESUS | 2016-03-04 20:03:27 | query reboot pgsql 9.5.1 |