Re: query reboot pgsql 9.5.1 - Mailing list pgsql-general
From | Felipe de Jesús Molina Bravo |
---|---|
Subject | Re: query reboot pgsql 9.5.1 |
Date | |
Msg-id | CALrs2KOojEd3g6dccw=Hh-mdzo8VroQLDRMHgCQ9Y6mt=ZAfJQ@mail.gmail.com Whole thread Raw |
In response to | Re: query reboot pgsql 9.5.1 (Melvin Davidson <melvin6925@gmail.com>) |
List | pgsql-general |
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)
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>:
I suspect your 9.5.1 database has not been analyzed yet and therefore the statistics are off.On Fri, Mar 4, 2016 at 3:52 PM, Felipe de Jesús Molina Bravo <fjmolinabravo@gmail.com> wrote: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?
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))yes these are differentsSo is each Postgres instance running in a separate container and if so are they set up the same?Yes, is the same configuration!!
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.
pgsql-general by date: