Re: 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:
>
> http://www.postgresql.org/message-id/CALrs2KPMowV6juLdOkMRq_P3MA5VkUmhdM4Q1OD0vCf2qimFfA@mail.gmail.
> com
>
> thanks in advance!


--
Adrian Klaver
adrian.klaver@aklaver.com