Date: Fri, 26 Sep 2014 15:04:08 +0100 (GMT+01:00)
From: =?utf-8?Q?Mat=C3=BA=C5=A1_Svr=C4=8Dek?= <svrcek@plaintext.sk>
To: pgsql-performance@postgresql.org
Message-ID: <702132002.23621411740248006.JavaMail.root@shiva>
In-Reply-To: <1342030349.23601411740174703.JavaMail.root@shiva>
Subject: after upgrade 8.4->9.3 query is slow not using index scan
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hello,
I am having a performance issue after upgrade from 8.4.20-1 -> 9.3.5. I am =
running on CentOS 2.6.32-431.29.2.el6.x86_64 #1 SMP Tue Sep 9 21:36:05 UTC =
2014 x86_64 x86_64 x86_64 GNU/Linux.
Upgrade was without any issues, I used pg_upgrade.

One of my queries now takes cca 100x more time than it used to. The query i=
s:
http://pastebin.com/uUe16SkR

explain from postgre 8.4.20-1:
http://pastebin.com/r3WRHzSM

explain from postgre 9.3.5:
http://pastebin.com/hmNxFiDL

The problematic part seems to be this (postgresql 93 version):
 SubPlan 17
                             ->  Limit  (cost=3D8.29..8.41 rows=3D1 width=
=3D11)
                                   InitPlan 16 (returns $19)
                                     ->  Index Scan using t_store_info_pkey=
 on t_store_info s_7  (cost=3D0.28..8.29 rows=3D1 width=3D8)
                                           Index Cond: (id =3D 87::bigint)
                                   ->  Nested Loop  (cost=3D0.00..72351.91 =
rows=3D624663 width=3D11)
                                         ->  Seq Scan on t_pn pn  (cost=3D0=
.00..37498.65 rows=3D1 width=3D11) <<-----!!!!
                                               Filter: ((max(w.item_ean) =
=3D ean) AND (company_fk =3D $19))
                                         ->  Seq Scan on t_weighting w4  (c=
ost=3D0.00..28606.63 rows=3D624663 width=3D0)

this row: Seq Scan on t_pn pn  (cost=3D0.00..37498.65 rows=3D1 width=3D11) =
in 8.4 explain looks like this:
->  Index Scan using inx_pn_companyfk_ean on t_pn pn  (cost=3D0.00..8.64 ro=
ws=3D1 width=3D11)
                                   Index Cond: ((company_fk =3D $19) AND ($=
20 =3D ean))

As You can see, 8.4 is using index scan on the table, 9.3 is using seq scan=
. The relevant index does exist in both databases.
So I tried to force 9.3 to use the index by:
set enable_seqscan =3D off;

Now explain analyze looks like this:
http://pastebin.com/kR7qr39u

the relevant problematic part is:
 SubPlan 17w.stat_count_entered IS NULL AND w.stat_weight_start IS NULL))
                             ->  Limit  (cost=3D9.15..9.31 rows=3D1 width=
=3D11)
                                   InitPlan 16 (returns $19)
                                     ->  Index Scan using t_store_info_pkey=
 on t_store_info s_7  (cost=3D0.28..8.29 rows=3D1 width=3D8)
                                           Index Cond: (id =3D 87::bigint)
                                   ->  Nested Loop  (cost=3D0.85..102881.78=
 rows=3D624667 width=3D11)
                                         ->  Index Only Scan using int_t_we=
ighting_coordinates on t_weighting w4  (cost=3D0.42..95064.99 rows=3D624667=
 <<---- !!!
                                         ->  Materialize  (cost=3D0.43..8.4=
5 rows=3D1 width=3D11)
                                               ->  Index Scan using inx_pn_=
companyfk_ean on t_pn pn  (cost=3D0.43..8.45 rows=3D1 width=3D11)
                                                     Index Cond: ((company_=
fk =3D $19) AND (max(w.item_ean) =3D ean))

So planner is now using index scan.

Query execution time with this is around 4.2 s (roughly same as in postgre =
8.4) , with enable_seqscan=3Don it is around 360s (2 orders of magnitude hi=
gher than with postgre 8.4). What is interesting is, that query cost is rou=
ghly the same in both situations.

My questions are:
 1. how to set postgresql / modify query / create some indexes / whatever, =
to get the same query running time in postgresql 9.3 as I had in 8.4
 2. how is it possible for analyze to get same costs when the query running=
 time is almost 100x higher.

Thank You for any ideas on this.
--
Mat=C3=BA=C5=A1 Svr=C4=8Dek
svrcek@plaintext.sk


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance