Thread: after upgrade 8.4->9.3 query is slow not using index scan

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
#1SMP 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 is:
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=8.29..8.41 rows=1 width=11)
                                   InitPlan 16 (returns $19)
                                     ->  Index Scan using t_store_info_pkey on t_store_info s_7  (cost=0.28..8.29
rows=1width=8) 
                                           Index Cond: (id = 87::bigint)
                                   ->  Nested Loop  (cost=0.00..72351.91 rows=624663 width=11)
                                         ->  Seq Scan on t_pn pn  (cost=0.00..37498.65 rows=1 width=11) <<-----!!!!
                                               Filter: ((max(w.item_ean) = ean) AND (company_fk = $19))
                                         ->  Seq Scan on t_weighting w4  (cost=0.00..28606.63 rows=624663 width=0)

this row: Seq Scan on t_pn pn  (cost=0.00..37498.65 rows=1 width=11) in 8.4 explain looks like this:
->  Index Scan using inx_pn_companyfk_ean on t_pn pn  (cost=0.00..8.64 rows=1 width=11)
                                   Index Cond: ((company_fk = $19) AND ($20 = 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 = 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=9.15..9.31 rows=1 width=11)
                                   InitPlan 16 (returns $19)
                                     ->  Index Scan using t_store_info_pkey on t_store_info s_7  (cost=0.28..8.29
rows=1width=8) 
                                           Index Cond: (id = 87::bigint)
                                   ->  Nested Loop  (cost=0.85..102881.78 rows=624667 width=11)
                                         ->  Index Only Scan using int_t_weighting_coordinates on t_weighting w4
(cost=0.42..95064.99rows=624667 <<---- !!! 
                                         ->  Materialize  (cost=0.43..8.45 rows=1 width=11)
                                               ->  Index Scan using inx_pn_companyfk_ean on t_pn pn  (cost=0.43..8.45
rows=1width=11) 
                                                     Index Cond: ((company_fk = $19) AND (max(w.item_ean) = 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=on it is around
360s(2 orders of magnitude higher than with postgre 8.4). What is interesting is, that query cost is roughly the same
inboth situations. 

My questions are:
 1. how to set postgresql / modify query / create some indexes / whatever, to get the same query running time in
postgresql9.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úš Svrček
svrcek@plaintext.sk