Обсуждение: query reboot pgsql 9.5.1

Поиск
Список
Период
Сортировка

query reboot pgsql 9.5.1

От
Felipe de Jesús Molina Bravo
Дата:
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!

Re: query reboot pgsql 9.5.1

От
Adrian Klaver
Дата:
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

Re: query reboot pgsql 9.5.1

От
Felipe de Jesús Molina Bravo
Дата:


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!!


Re: query reboot pgsql 9.5.1

От
Melvin Davidson
Дата:


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.

Re: query reboot pgsql 9.5.1

От
"David G. Johnston"
Дата:
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.
​
 

Re: query reboot pgsql 9.5.1

От
Felipe de Jesús Molina Bravo
Дата:
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 estimadas
ANALYZE
pba=# 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 estimadas
ANALYZE
pba=# 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>:


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.

Re: query reboot pgsql 9.5.1

От
Felipe de Jesús Molina Bravo
Дата:
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 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.
​
 

Re: query reboot pgsql 9.5.1

От
"David G. Johnston"
Дата:
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.
​
 

Re: query reboot pgsql 9.5.1

От
Felipe de Jesús Molina Bravo
Дата:
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>:
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.
​
 

Re: query reboot pgsql 9.5.1

От
Alvaro Herrera
Дата:
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

Re: query reboot pgsql 9.5.1

От
Felipe de Jesús Molina Bravo
Дата:
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))


Re: query reboot pgsql 9.5.1

От
"David G. Johnston"
Дата:
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.
​

Re: query reboot pgsql 9.5.1

От
Felipe de Jesús Molina Bravo
Дата:
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>:
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.
​


Re: query reboot pgsql 9.5.1

От
"David G. Johnston"
Дата:

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.
​​
 

Re: query reboot pgsql 9.5.1

От
"David G. Johnston"
Дата:
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.
​

Re: query reboot pgsql 9.5.1

От
Alvaro Herrera
Дата:
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