query reboot pgsql 9.5.1

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:

/messages/by-id/CALrs2KPMowV6juLdOkMRq_P3MA5VkUmhdM4Q1OD0vCf2qimFfA@mail.gmail.
com

thanks in advance!

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

yes these are differents

So is each Postgres instance running in a separate container and if so are
they set up the same?

Yes, is the same configuration!!

On Fri, Mar 4, 2016 at 3:52 PM, Felipe de Jesús Molina Bravo <
fjmolinabravo@gmail.com> wrote:

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?

yes these are differents

So is each Postgres instance running in a separate container and if so
are they set up the same?

Yes, is the same configuration!!

I suspect your 9.5.1 database has not been analyzed yet and therefore the
statistics are off.

Do the following in the 9.5.1 database and then retry your query.

ANALYZE VERBOSE public._gc_cat;
ANALYZE VERBOSE public._gc_tb;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Fri, Mar 4, 2016 at 1:52 PM, Felipe de Jesús Molina Bravo <
fjmolinabravo@gmail.com> wrote:

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?

yes these are differents

​It would be nice to see the output of "EXPLAIN (ANALYZE, TIMING, BUFFERS)"
so real timings can be observed.

David J.
​

the result was the same:

*pba=# ANALYZE VERBOSE public._gc_cat;INFO: analizando
«public._gc_cat»INFO: «_gc_cat»: se procesaron 1999 de 1999 páginas, que
contenían 91932 filas vigentes y 0 filas no vigentes; 30000 filas en la
muestra, 91932 total de filas estimadasANALYZEpba=# ANALYZE VERBOSE
public._gc_;public._gc_cat public._gc_tb pba=# ANALYZE VERBOSE
public._gc_tb;INFO: analizando «public._gc_tb»INFO: «_gc_tb»: se
procesaron 2120 de 2120 páginas, que contenían 120130 filas vigentes y 0
filas no vigentes; 30000 filas en la muestra, 120130 total de filas
estimadasANALYZEpba=# SELECT idprodxintegrar FROM _gc_tb a LEFT join
_gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );Terminado
(killed)*

2016-03-04 15:00 GMT-06:00 Melvin Davidson <melvin6925@gmail.com>:

the output is:

pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb
a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama )
;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..0.01 rows=1 width=4) (actual
time=0.003..0.003 rows=0 loops=1)
Join Filter: ((b.arama <@ a.arama) AND (a.arama <@ b.arama))
-> Seq Scan on _gc_tb a (cost=0.00..0.00 rows=1 width=66) (actual
time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on _gc_cat b (cost=0.00..0.00 rows=1 width=70) (never
executed)
Planning time: 0.206 ms
Execution time: 0.074 ms
(6 filas)

2016-03-04 15:01 GMT-06:00 David G. Johnston <david.g.johnston@gmail.com>:

On Fri, Mar 4, 2016 at 2:16 PM, Felipe de Jesús Molina Bravo <
fjmolinabravo@gmail.com> wrote:

the output is:

pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM
_gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@
b.arama )
;
QUERY
PLAN

---------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..0.01 rows=1 width=4) (actual
time=0.003..0.003 rows=0 loops=1)
Join Filter: ((b.arama <@ a.arama) AND (a.arama <@ b.arama))
-> Seq Scan on _gc_tb a (cost=0.00..0.00 rows=1 width=66) (actual
time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on _gc_cat b (cost=0.00..0.00 rows=1 width=70) (never
executed)
Planning time: 0.206 ms
Execution time: 0.074 ms
(6 filas)

​OK, so this _gc_tb is empty which means that an extremely fast execution
time is not unsurprising. If the other version actually contains data I
would expect that it would take considerably longer...

David J.
​

sorry...i made a mistake...my tables are unlogged
and in the last test these was wiped... :(

when i create the tables again (with all record) the result is:

pba=# \i tablas.sql
DROP TABLE
SELECT 120130
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
SELECT 91932
CREATE INDEX
CREATE INDEX
pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb
a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama )
;
Terminado (killed)

:(

2016-03-04 15:30 GMT-06:00 David G. Johnston <david.g.johnston@gmail.com>:

Felipe de Jes�s Molina Bravo wrote:

pba=# \i tablas.sql
DROP TABLE
SELECT 120130
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
SELECT 91932
CREATE INDEX
CREATE INDEX
pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb
a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama )
;
Terminado (killed)

It used up so much memory that the OOM-killer terminated it. That
wouldn't happen with the plan you previously showed, so please do the
same without the ANALYZE option to see what plan is it trying to
execute.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

when i run without the ANALIZE the output is:

pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a LEFT
join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );
ERROR: la opción BUFFERS de EXPLAIN requiere ANALYZE
pba=# EXPLAIN ( TIMING) SELECT idprodxintegrar FROM _gc_tb a LEFT join
_gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );
ERROR: la opción TIMING de EXPLAIN requiere ANALYZE

if i run only with EXPLAIN the output is:

QUERY
PLAN
--------------------------------------------------------------------------------------
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))

On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo <
fjmolinabravo@gmail.com> wrote:

when i run without the ANALIZE the output is:

pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a
LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );
ERROR: la opción BUFFERS de EXPLAIN requiere ANALYZE
pba=# EXPLAIN ( TIMING) SELECT idprodxintegrar FROM _gc_tb a LEFT join
_gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );
ERROR: la opción TIMING de EXPLAIN requiere ANALYZE

​What is it about those error messages that is confusing you?​

​David J.
​

No, these messages are not confuse; I just wanted to show output

2016-03-04 15:58 GMT-06:00 David G. Johnston <david.g.johnston@gmail.com>:

2016-03-04 15:58 GMT-06:00 David G. Johnston <david.g.johnston@gmail.com>:

On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo <
fjmolinabravo@gmail.com> wrote:

when i run without the ANALIZE the output is:

pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a
LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );
ERROR: la opción BUFFERS de EXPLAIN requiere ANALYZE
pba=# EXPLAIN ( TIMING) SELECT idprodxintegrar FROM _gc_tb a LEFT join
_gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );
ERROR: la opción TIMING de EXPLAIN requiere ANALYZE

​What is it about those error messages that is confusing you?​

​
On Fri, Mar 4, 2016 at 3:06 PM, Felipe de Jesús Molina Bravo <
fjmolinabravo@gmail.com> wrote:

No, these messages are not confuse; I just wanted to show output

​Then when don't you add "ANALYZE" to those commands and show meaningful
output instead of showing us useless errors?​

David J.
​​

On Fri, Mar 4, 2016 at 3:09 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

2016-03-04 15:58 GMT-06:00 David G. Johnston <david.g.johnston@gmail.com>
:

On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo <
fjmolinabravo@gmail.com> wrote:

when i run without the ANALIZE the output is:

pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a
LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );
ERROR: la opción BUFFERS de EXPLAIN requiere ANALYZE
pba=# EXPLAIN ( TIMING) SELECT idprodxintegrar FROM _gc_tb a LEFT join
_gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama );
ERROR: la opción TIMING de EXPLAIN requiere ANALYZE

​What is it about those error messages that is confusing you?​

​
On Fri, Mar 4, 2016 at 3:06 PM, Felipe de Jesús Molina Bravo <
fjmolinabravo@gmail.com> wrote:

No, these messages are not confuse; I just wanted to show output

​Then when don't you add "ANALYZE" to those commands and show meaningful
output instead of showing us useless errors?​

​To be more clear, you need to run a query that will complete in our
lifetime (and without an OOM error) with all three of ANALYZE, BUFFERS, and
TIMING specified for the EXPLAIN.

David J.
​

David G. Johnston wrote:

​To be more clear, you need to run a query that will complete in our
lifetime (and without an OOM error) with all three of ANALYZE, BUFFERS, and
TIMING specified for the EXPLAIN.

I think the problem is pretty clear. The plan is sensible yet the
result doesn't seem to be. Why do you think using up all the memory is
a sensible result here?

Jaime Casanova suggested that maybe the @> operator have memory leaks.
Or perhaps the GIN index machinery that's using them.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general