Home > mailing lists

query reboot pgsql 9.5.1 - Mailing list pgsql-general

From	Felipe de Jesús Molina Bravo
Subject	query reboot pgsql 9.5.1
Date	March 4, 2016 23:09:42
Msg-id	CALrs2KMK9rjuL-6_ubes7ca62hXcz6M-vLF2vX+n9Gtihj0HsQ@mail.gmail.com Whole thread Raw
Responses	Re: query reboot pgsql 9.5.1 (Adrian Klaver <adrian.klaver@aklaver.com>)
List	pgsql-general

Tree view

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!

pgsql-general by date:

From: "David G. Johnston"
Date: 04 March 2016, 21:33:45
Subject: Re: multiple UNIQUE indices for FK

From: Adrian Klaver
Date: 04 March 2016, 23:43:56
Subject: Re: query reboot pgsql 9.5.1

query reboot pgsql 9.5.1 - Mailing list pgsql-general

Previous

Next