pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedafter upgrade 8.4->9.3 query is slow not using index scan
3+ messages / 2 participants
[nested] [flat]
* after upgrade 8.4->9.3 query is slow not using index scan
@ 2014-09-26 14:04 Matúš Svrček <svrcek@plaintext.sk>
0 siblings, 1 reply; 3+ messages in thread
From: Matúš Svrček @ 2014-09-26 14:04 UTC (permalink / raw)
To: pgsql-performance
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 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=1 width=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=1 width=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.99 rows=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=1 width=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 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úš Svrček
svrcek@plaintext.sk
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: after upgrade 8.4->9.3 query is slow not using index scan
@ 2014-09-26 19:03 Victor Yegorov <vyegorov@gmail.com>
parent: Matúš Svrček <svrcek@plaintext.sk>
0 siblings, 0 replies; 3+ messages in thread
From: Victor Yegorov @ 2014-09-26 19:03 UTC (permalink / raw)
To: Matúš Svrček <svrcek@plaintext.sk>; +Cc: pgsql-performance
2014-09-26 17:04 GMT+03:00 Matúš Svrček <svrcek@plaintext.sk>:
> I am having a performance issue after upgrade from 8.4.20-1 -> 9.3.5.
First, make sure you have your statistics up to date — execute manual
`VACUUM ANALYZE`.
And then provide `EXPLAIN analyze` for 8.4 and `EXPLAIN (analyze, buffers)`
for 9.3 output.
--
Victor Y. Yegorov
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: after upgrade 8.4->9.3 query is slow not using index scan
@ 2014-09-28 20:24 Matúš Svrček <svrcek@plaintext.sk>
0 siblings, 0 replies; 3+ messages in thread
From: Matúš Svrček @ 2014-09-28 20:24 UTC (permalink / raw)
To: Victor Yegorov <vyegorov@gmail.com>; +Cc: pgsql-performance
Hello,
at the moment, I am not able to replicate the results posted. Explain analyze results are very similar at the moment on both databases, query runtime is also almost same.
This has happened after re-running VACUUM ANALYZE VERBOSE. However, right after the migration, I did run the script, which was generated by pg_upgrade script, which should have ran VACUUM on the database, the script name is analyze_new_cluster.sh.
Maybe after re-running vacuum analyze the statistics somehow changed?
Case closed, thank You.
--
Matúš Svrček
PlainText s.r.o. [www.plaintext.sk]
svrcek@plaintext.sk
----- "Victor Yegorov" <vyegorov@gmail.com> wrote:
> 2014-09-26 17:04 GMT+03:00 Matúš Svrček < svrcek@plaintext.sk > :
>
>
> I am having a performance issue after upgrade from 8.4.20-1 -> 9.3.5.
>
>
> First, make sure you have your statistics up to date — execute manual
> `VACUUM ANALYZE`.
> And then provide `EXPLAIN analyze` for 8.4 and `EXPLAIN (analyze,
> buffers)` for 9.3 output.
>
>
>
> --
> Victor Y. Yegorov
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-09-28 20:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-09-26 14:04 after upgrade 8.4->9.3 query is slow not using index scan Matúš Svrček <svrcek@plaintext.sk>
2014-09-26 19:03 ` Victor Yegorov <vyegorov@gmail.com>
2014-09-28 20:24 Re: after upgrade 8.4->9.3 query is slow not using index scan Matúš Svrček <svrcek@plaintext.sk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox