Ygthkb

C++ range based loop with special case for first item?

Crack the bank account's password!

Is the tritone (A4 / d5) still banned in Roman Catholic music?

Are all power cords made equal?

How to transport 10,000 terrestrial trolls across ocean fast?

bash aliases do not expand even with shopt expand_aliases

Is there any danger of my neighbor having my wife's signature?

Canadian Visa by land

Disk space full during insert, what happens?

Expression for "unconsciously using words (or accents) used by a person you often talk with or listen to"?

How bad is a Computer Science course that doesn't teach Design Patterns?

What is the Buddhist view in Socratic questioning?

Identify non-coding regions from a genome annotation

Why can all solutions to the simple harmonic motion equation be written in terms of sines and cosines?

Converting numbers to words - Python

What is this mysterious *green square* on my Content Editor?

Can an attached stirge deal no more than 10 HP of damage before it detaches?

How to make transparent background from pdf to png

How can changes in personality/values of a person who turned into a vampire be explained?

Including proofs of known theorems in master's thesis

How can I prep for the Curse of Strahd adventure effectively?

Is there any way to play D&D without a DM?

Graphical modeler calculator missing in QGIS3

Sed-Grep-Awk operations

Weird query execution performance

Sampling and matching samples with spatial conditionsCan't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQLBad performance with select on view if check for 'is null'Postgres query optimization: why could two very similar join tables have vastly different querying times?Optimise a LATERAL JOIN query on a big tableWhich index shall I create in Postgres to speedup join of two big (several million rows) tables connected one-to-many?Slow postgres query that uses quick sort instead of hash-aggregateVery slow simple JOIN querySlow query times for similarity searches with pg_trgm indicesDoes a view use a partition in PostgreSql?

0

I have a logical replication in two distinct databases in different machines. A query in one of those executes in 13 seconds, the same in another takes more than 13 minutes to finish.

The two machines have the same tables, indices, postgres version (10.6),
postgres conf, OS version and similar hardware configuration.

Execution plan:

Fast: https://explain.depesz.com/s/12rd

Slow: https://explain.depesz.com/s/v7ep

These are the indexes:

create index on tmp(cpf_cnpj);

create index on cliente_telefone(cpf_cnpj);

create index on cliente_telefone(id_telefone,cpf_cnpj);

create index on telefone(id_telefone);

create index on telefone(ddd, numero);

And this is the query:

    select b.cpf_cnpj, 

       b.ddd, 

       b.numero,

       'bom' as id_resultado

       from tmp as b

       left join cliente_telefone as ct on ct.cpf_cnpj =                b.cpf_cnpj

inner join telefone as t on t.ddd = b.ddd and t.numero = b.numero

where

      b.status in ('BESTFONE', 'Bom') and

      ct.id_telefone is null;

I have already made vacuum full, reindex, set some settings like random page cost, disable and enable seqscan, disable and enable indexscan, nothing solved the problem.

postgresql postgresql-10

share|improve this question

asked 10 mins ago

Gabriel NothGabriel Noth

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You you show us the plans generated using explain (analyze, buffers)?
  
  – a_horse_with_no_name
  8 mins ago
  

  
  
  
  

add a comment | 

0

I have a logical replication in two distinct databases in different machines. A query in one of those executes in 13 seconds, the same in another takes more than 13 minutes to finish.

The two machines have the same tables, indices, postgres version (10.6),
postgres conf, OS version and similar hardware configuration.

Execution plan:

Fast: https://explain.depesz.com/s/12rd

Slow: https://explain.depesz.com/s/v7ep

These are the indexes:

create index on tmp(cpf_cnpj);

create index on cliente_telefone(cpf_cnpj);

create index on cliente_telefone(id_telefone,cpf_cnpj);

create index on telefone(id_telefone);

create index on telefone(ddd, numero);

And this is the query:

    select b.cpf_cnpj, 

       b.ddd, 

       b.numero,

       'bom' as id_resultado

       from tmp as b

       left join cliente_telefone as ct on ct.cpf_cnpj =                b.cpf_cnpj

inner join telefone as t on t.ddd = b.ddd and t.numero = b.numero

where

      b.status in ('BESTFONE', 'Bom') and

      ct.id_telefone is null;

I have already made vacuum full, reindex, set some settings like random page cost, disable and enable seqscan, disable and enable indexscan, nothing solved the problem.

postgresql postgresql-10

share|improve this question

asked 10 mins ago

Gabriel NothGabriel Noth

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You you show us the plans generated using explain (analyze, buffers)?
  
  – a_horse_with_no_name
  8 mins ago
  

  
  
  
  

add a comment | 

I have a logical replication in two distinct databases in different machines. A query in one of those executes in 13 seconds, the same in another takes more than 13 minutes to finish.

The two machines have the same tables, indices, postgres version (10.6),
postgres conf, OS version and similar hardware configuration.

Execution plan:

Fast: https://explain.depesz.com/s/12rd

Slow: https://explain.depesz.com/s/v7ep

These are the indexes:

create index on tmp(cpf_cnpj);

create index on cliente_telefone(cpf_cnpj);

create index on cliente_telefone(id_telefone,cpf_cnpj);

create index on telefone(id_telefone);

create index on telefone(ddd, numero);

And this is the query:

    select b.cpf_cnpj, 

       b.ddd, 

       b.numero,

       'bom' as id_resultado

       from tmp as b

       left join cliente_telefone as ct on ct.cpf_cnpj =                b.cpf_cnpj

inner join telefone as t on t.ddd = b.ddd and t.numero = b.numero

where

      b.status in ('BESTFONE', 'Bom') and

      ct.id_telefone is null;

I have already made vacuum full, reindex, set some settings like random page cost, disable and enable seqscan, disable and enable indexscan, nothing solved the problem.

postgresql postgresql-10

share|improve this question

asked 10 mins ago

Gabriel NothGabriel Noth

1

create index on tmp(cpf_cnpj);

create index on cliente_telefone(cpf_cnpj);

create index on cliente_telefone(id_telefone,cpf_cnpj);

create index on telefone(id_telefone);

    select b.cpf_cnpj, 

       b.ddd, 

       b.numero,

       'bom' as id_resultado

       from tmp as b

       left join cliente_telefone as ct on ct.cpf_cnpj =                b.cpf_cnpj

inner join telefone as t on t.ddd = b.ddd and t.numero = b.numero

where

      b.status in ('BESTFONE', 'Bom') and

      ct.id_telefone is null;

share|improve this question

asked 10 mins ago

Gabriel NothGabriel Noth

1

share|improve this question

asked 10 mins ago

Gabriel NothGabriel Noth

1

asked 10 mins ago

Gabriel NothGabriel Noth

1

asked 10 mins ago

Gabriel NothGabriel Noth

1