Thank you Tom and David for your very helpful replies. We dumped and restored the RDS staging database on a local installation of pg and were not able to reproduce the issue in 9.5.2, which led us to try running a VACUUM ANALYZE and recreating indexes. After this we no longer saw a discrepancy between the query plan in the separate environments. Is this what you meant, Tom, by making sure to ANALYZE? Or did you mean including ANALYZE in EXPLAIN ANALYZE? On 6/17/16, 11:17 AM, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote: >"Erdmann, Markus @ Bellevue" <Markus.Erdmann@xxxxxxxx> writes: >> We¹re trying to debug a performance issue affecting our staging >>database, and we¹ve narrowed it down to a difference in the query >>optimizer in 9.5.2. Upgrading to 9.5 is important for us because we need >>the ability to import foreign schemas. > >I think the core of the problem is the large rowcount estimation error >here: > >> -> Bitmap Index Scan on >>transactions_transaction_c69e55a4 (cost=0.00..18.02 rows=161 width=0) >>(actual time=20.153..20.153 rows=269021 loops=1) >> Index Cond: ((date_created >= >>'2010-01-01'::date) AND (date_created <= '2015-12-31'::date)) > >That's a pretty simple condition and it's hard to believe that 9.5 does it >much differently than 9.4 did. Perhaps you forgot to ANALYZE, or were >using a larger statistics target in the 9.4 installation? > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
