Thread: Slow query after upgrade from 9.0 to 9.2

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

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

--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

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

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

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