Slow query after upgrade from 9.0 to 9.2

[Andrzej Zawadzki]

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.

[Jeff Janes]

Post by Andrzej Zawadzki
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

[Andrzej Zawadzki]

Post by Jeff Janes

Post by Andrzej Zawadzki
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.

Post by Jeff Janes
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.

[Jeff Janes]

Post by Andrzej Zawadzki

Post by Jeff Janes
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

[Matheus de Oliveira]

Post by Andrzej Zawadzki
Hi!
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,

[Andrzej Zawadzki]

Post by Andrzej Zawadzki
Hi!
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:[phone]>
"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:[phone]>)"
" 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:[phone]>)"
" 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.

Post by Andrzej Zawadzki
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