pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query after upgrade from 9.0 to 9.2
6+ messages / 3 participants
[nested] [flat]
* Slow query after upgrade from 9.0 to 9.2
@ 2013-01-10 13:32 Andrzej Zawadzki <zawadaa@wp.pl>
2013-01-10 18:17 ` Re: Slow query after upgrade from 9.0 to 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-01-10 18:48 ` Re: Slow query after upgrade from 9.0 to 9.2 Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Andrzej Zawadzki @ 2013-01-10 13:32 UTC (permalink / raw)
To: pgsql-performance
Hi!
Small query run on 9.0 very fast:
SELECT * from sygma_arrear sar where sar.arrear_import_id = (
select sa.arrear_import_id from sygma_arrear sa, arrear_import ai
where sa.arrear_flag_id = 2
AND sa.arrear_import_id = ai.id
AND ai.import_type_id = 1
order by report_date desc limit 1)
AND sar.arrear_flag_id = 2
AND sar.credit_id = 3102309
"Index Scan using sygma_arrear_credit_id on sygma_arrear sar
(cost=0.66..362.03 rows=1 width=265)"
" Index Cond: (credit_id = 3102309)"
" Filter: ((arrear_import_id = $0) AND (arrear_flag_id = 2))"
" InitPlan 1 (returns $0)"
" -> Limit (cost=0.00..0.66 rows=1 width=8)"
" -> Nested Loop (cost=0.00..3270923.14 rows=4930923 width=8)"
" -> Index Scan Backward using report_date_bank_id_key
on arrear_import ai (cost=0.00..936.87 rows=444 width=8)"
" Filter: (import_type_id = 1)"
*" -> Index Scan using sygma_arrear_arrear_import_id_idx
on sygma_arrear sa (cost=0.00..6971.15 rows=31495 width=4)"**
**" Index Cond: (sa.arrear_import_id = ai.id)"**
**" Filter: (sa.arrear_flag_id = 2)"**
*
Engine uses index - great.
On 9.2
"Index Scan using sygma_arrear_credit_id on sygma_arrear sar
(cost=11.05..381.12 rows=1 width=265)"
" Index Cond: (credit_id = 3102309)"
" Filter: ((arrear_import_id = $0) AND (arrear_flag_id = 2))"
" InitPlan 1 (returns $0)"
" -> Limit (cost=0.00..11.05 rows=1 width=8)"
" -> Nested Loop (cost=0.00..54731485.84 rows=4953899 width=8)"
" Join Filter: (sa.arrear_import_id = ai.id)"
" -> Index Scan Backward using report_date_bank_id_key
on arrear_import ai (cost=0.00..62.81 rows=469 width=8)"
" Filter: (import_type_id = 1)"
*" -> Materialize (cost=0.00..447641.42 rows=6126357
width=4)"**
**" -> Seq Scan on sygma_arrear sa
(cost=0.00..393077.64 rows=6126357 width=4)"**
**" Filter: (arrear_flag_id = 2)"**
*
Seq scan... slooow.
Why that's happens? All configurations are identical. Only engine is
different.
When I make index on to column: (arrear_import_id,arrear_flag_id) then
engine use it and run fast.
--
Andrzej Zawadzki
--
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] 6+ messages in thread
* Re: Slow query after upgrade from 9.0 to 9.2
2013-01-10 13:32 Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
@ 2013-01-10 18:17 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-11 08:13 ` Re: Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
1 sibling, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2013-01-10 18:17 UTC (permalink / raw)
To: Andrzej Zawadzki <zawadaa@wp.pl>; +Cc: pgsql-performance
On Thu, Jan 10, 2013 at 5:32 AM, Andrzej Zawadzki <zawadaa@wp.pl> wrote:
>
> Why that's happens? All configurations are identical. Only engine is
> different.
Could you post explain (analyze, buffers) instead of just explain?
Also, if you temporarily set enable_seqscan=off on 9.2, what plan do
you then get?
Cheers,
Jeff
--
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] 6+ messages in thread
* Re: Slow query after upgrade from 9.0 to 9.2
2013-01-10 13:32 Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
2013-01-10 18:17 ` Re: Slow query after upgrade from 9.0 to 9.2 Jeff Janes <jeff.janes@gmail.com>
@ 2013-01-11 08:13 ` Andrzej Zawadzki <zawadaa@wp.pl>
2013-01-11 17:12 ` Re: Slow query after upgrade from 9.0 to 9.2 Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrzej Zawadzki @ 2013-01-11 08:13 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 10.01.2013 19:17, Jeff Janes wrote:
> On Thu, Jan 10, 2013 at 5:32 AM, Andrzej Zawadzki <zawadaa@wp.pl> wrote:
>> Why that's happens? All configurations are identical. Only engine is
>> different.
> Could you post explain (analyze, buffers) instead of just explain?
Impossible, 1h of waiting and I've killed that.
> Also, if you temporarily set enable_seqscan=off on 9.2, what plan do
> you then get?
Plan is different.
"Index Scan using sygma_arrear_credit_id on sygma_arrear sar
(cost=11.07..390.66 rows=1 width=265)"
" Index Cond: (credit_id = 3102309)"
" Filter: ((arrear_import_id = $0) AND (arrear_flag_id = 2))"
" InitPlan 1 (returns $0)"
" -> Limit (cost=0.00..11.07 rows=1 width=8)"
" -> Nested Loop (cost=0.00..54961299.49 rows=4963314 width=8)"
" Join Filter: (sa.arrear_import_id = ai.id)"
" -> Index Scan Backward using report_date_bank_id_key
on arrear_import ai (cost=0.00..62.81 rows=469 width=8)"
" Filter: (import_type_id = 1)"
" -> Materialize (cost=0.00..574515.68 rows=6138000
width=4)"
" -> Index Scan using
sygma_arrear_arrear_import_id_idx on sygma_arrear sa
(cost=0.00..519848.68 rows=6138000 width=4)"
" Filter: (arrear_flag_id = 2)"
The real query is still slow.
--
Andrzej Zawadzki
--
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] 6+ messages in thread
* Re: Slow query after upgrade from 9.0 to 9.2
2013-01-10 13:32 Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
2013-01-10 18:17 ` Re: Slow query after upgrade from 9.0 to 9.2 Jeff Janes <jeff.janes@gmail.com>
2013-01-11 08:13 ` Re: Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
@ 2013-01-11 17:12 ` Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Janes @ 2013-01-11 17:12 UTC (permalink / raw)
To: Andrzej Zawadzki <zawadaa@wp.pl>; +Cc: pgsql-performance
On Fri, Jan 11, 2013 at 12:13 AM, Andrzej Zawadzki <zawadaa@wp.pl> wrote:
> On 10.01.2013 19:17, Jeff Janes wrote:
>> Also, if you temporarily set enable_seqscan=off on 9.2, what plan do
>> you then get?
>
> Plan is different.
>
> " Join Filter: (sa.arrear_import_id = ai.id)"
It is hard to imagine why it is not using
sygma_arrear_arrear_import_id_idx for this given the plan is now
accessing the index anyway. Have the types or encodings or collations
somehow become incompatible so that this index can no longer fulfill
it?
What if you just write a very simple join between the two tables with
the above join condition, with another highly selective condition on
ai?
Cheers,
Jeff
--
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] 6+ messages in thread
* Re: Slow query after upgrade from 9.0 to 9.2
2013-01-10 13:32 Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
@ 2013-01-10 18:48 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2013-01-11 08:23 ` Re: Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
1 sibling, 1 reply; 6+ messages in thread
From: Matheus de Oliveira @ 2013-01-10 18:48 UTC (permalink / raw)
To: Andrzej Zawadzki <zawadaa@wp.pl>; +Cc: pgsql-performance
On Thu, Jan 10, 2013 at 11:32 AM, Andrzej Zawadzki <zawadaa@wp.pl> wrote:
> Hi!
>
> Small query run on 9.0 very fast:
>
> SELECT * from sygma_arrear sar where sar.arrear_import_id = (
> select sa.arrear_import_id from sygma_arrear sa, arrear_import ai
> where sa.arrear_flag_id = 2
> AND sa.arrear_import_id = ai.id
> AND ai.import_type_id = 1
> order by report_date desc limit 1)
> AND sar.arrear_flag_id = 2
> AND sar.credit_id = 3102309
>
> "Index Scan using sygma_arrear_credit_id on sygma_arrear sar
> (cost=0.66..362.03 rows=1 width=265)"
> " Index Cond: (credit_id = 3102309)"
> " Filter: ((arrear_import_id = $0) AND (arrear_flag_id = 2))"
> " InitPlan 1 (returns $0)"
> " -> Limit (cost=0.00..0.66 rows=1 width=8)"
> " -> Nested Loop (cost=0.00..3270923.14 rows=4930923 width=8)"
> " -> Index Scan Backward using report_date_bank_id_key
> on arrear_import ai (cost=0.00..936.87 rows=444 width=8)"
> " Filter: (import_type_id = 1)"
> *" -> Index Scan using sygma_arrear_arrear_import_id_idx
> on sygma_arrear sa (cost=0.00..6971.15 rows=31495 width=4)"**
> **" Index Cond: (sa.arrear_import_id = ai.id)"**
> **" Filter: (sa.arrear_flag_id = 2)"**
> *
> Engine uses index - great.
>
> On 9.2
>
> "Index Scan using sygma_arrear_credit_id on sygma_arrear sar
> (cost=11.05..381.12 rows=1 width=265)"
> " Index Cond: (credit_id = 3102309)"
> " Filter: ((arrear_import_id = $0) AND (arrear_flag_id = 2))"
> " InitPlan 1 (returns $0)"
> " -> Limit (cost=0.00..11.05 rows=1 width=8)"
> " -> Nested Loop (cost=0.00..54731485.84 rows=4953899 width=8)"
> " Join Filter: (sa.arrear_import_id = ai.id)"
> " -> Index Scan Backward using report_date_bank_id_key
> on arrear_import ai (cost=0.00..62.81 rows=469 width=8)"
> " Filter: (import_type_id = 1)"
> *" -> Materialize (cost=0.00..447641.42 rows=6126357
> width=4)"**
> **" -> Seq Scan on sygma_arrear sa
> (cost=0.00..393077.64 rows=6126357 width=4)"**
> **" Filter: (arrear_flag_id = 2)"**
> *
> Seq scan... slooow.
>
> Why that's happens? All configurations are identical. Only engine is
> different.
>
>
How did you do the upgrade?
Have you tried to run a VACUUM ANALYZE on sygma_arrear?
Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow query after upgrade from 9.0 to 9.2
2013-01-10 13:32 Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
2013-01-10 18:48 ` Re: Slow query after upgrade from 9.0 to 9.2 Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2013-01-11 08:23 ` Andrzej Zawadzki <zawadaa@wp.pl>
0 siblings, 0 replies; 6+ messages in thread
From: Andrzej Zawadzki @ 2013-01-11 08:23 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: pgsql-performance
On 10.01.2013 19:48, Matheus de Oliveira wrote:
>
>
> On Thu, Jan 10, 2013 at 11:32 AM, Andrzej Zawadzki <zawadaa@wp.pl
> <mailto:zawadaa@wp.pl>> wrote:
>
> Hi!
>
> Small query run on 9.0 very fast:
>
> SELECT * from sygma_arrear sar where sar.arrear_import_id = (
> select sa.arrear_import_id from sygma_arrear sa,
> arrear_import ai
> where sa.arrear_flag_id = 2
> AND sa.arrear_import_id = ai.id <http://ai.id;
> AND ai.import_type_id = 1
> order by report_date desc limit 1)
> AND sar.arrear_flag_id = 2
> AND sar.credit_id = 3102309 <tel:3102309>
>
> "Index Scan using sygma_arrear_credit_id on sygma_arrear sar
> (cost=0.66..362.03 rows=1 width=265)"
> " Index Cond: (credit_id = 3102309 <tel:3102309>)"
> " Filter: ((arrear_import_id = $0) AND (arrear_flag_id = 2))"
> " InitPlan 1 (returns $0)"
> " -> Limit (cost=0.00..0.66 rows=1 width=8)"
> " -> Nested Loop (cost=0.00..3270923.14 rows=4930923
> width=8)"
> " -> Index Scan Backward using report_date_bank_id_key
> on arrear_import ai (cost=0.00..936.87 rows=444 width=8)"
> " Filter: (import_type_id = 1)"
> *" -> Index Scan using
> sygma_arrear_arrear_import_id_idx
> on sygma_arrear sa (cost=0.00..6971.15 rows=31495 width=4)"**
> **" Index Cond: (sa.arrear_import_id = ai.id
> <http://ai.id>)"**
> **" Filter: (sa.arrear_flag_id = 2)"**
> *
> Engine uses index - great.
>
> On 9.2
>
> "Index Scan using sygma_arrear_credit_id on sygma_arrear sar
> (cost=11.05..381.12 rows=1 width=265)"
> " Index Cond: (credit_id = 3102309 <tel:3102309>)"
> " Filter: ((arrear_import_id = $0) AND (arrear_flag_id = 2))"
> " InitPlan 1 (returns $0)"
> " -> Limit (cost=0.00..11.05 rows=1 width=8)"
> " -> Nested Loop (cost=0.00..54731485.84 rows=4953899
> width=8)"
> " Join Filter: (sa.arrear_import_id = ai.id
> <http://ai.id>)";
> " -> Index Scan Backward using report_date_bank_id_key
> on arrear_import ai (cost=0.00..62.81 rows=469 width=8)"
> " Filter: (import_type_id = 1)"
> *" -> Materialize (cost=0.00..447641.42 rows=6126357
> width=4)"**
> **" -> Seq Scan on sygma_arrear sa
> (cost=0.00..393077.64 rows=6126357 width=4)"**
> **" Filter: (arrear_flag_id = 2)"**
> *
> Seq scan... slooow.
>
> Why that's happens? All configurations are identical. Only engine is
> different.
>
>
>
> How did you do the upgrade?
pg_upgrade and I think that this is source of problem.
I have test database from dump/restore process and works properly.
> Have you tried to run a VACUUM ANALYZE on sygma_arrear?
Yes I did - after upgrade all databases was vacuumed.
vacuumdb -azv
I'll try reindex all indexes at weekend
--
Andrzej Zawadzki
--
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] 6+ messages in thread
end of thread, other threads:[~2013-01-11 17:12 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-10 13:32 Slow query after upgrade from 9.0 to 9.2 Andrzej Zawadzki <zawadaa@wp.pl>
2013-01-10 18:17 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-11 08:13 ` Andrzej Zawadzki <zawadaa@wp.pl>
2013-01-11 17:12 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-10 18:48 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2013-01-11 08:23 ` Andrzej Zawadzki <zawadaa@wp.pl>
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