Re: simple query running for ever

2020-06-16 Thread Justin Pryzby
On Wed, Jun 17, 2020 at 12:10:37AM +0200, Andreas Joseph Krogh wrote: > På onsdag 17. juni 2020 kl. 00:05:26, skrev Justin Pryzby > : > On Tue, Jun 16, 2020 at 08:35:31PM +, Nagaraj Raj wrote: > > I wrote a simple query, and it is taking too long, not sure what is wrong > in it, even its no

Re: simple query running for ever

2020-06-16 Thread Andreas Joseph Krogh
På onsdag 17. juni 2020 kl. 00:05:26, skrev Justin Pryzby mailto:pry...@telsasoft.com>>: On Tue, Jun 16, 2020 at 08:35:31PM +, Nagaraj Raj wrote: > I wrote a simple query, and it is taking too long, not sure what is wrong in it, even its not giving EXPLAIN ANALYZE. > > select T0."physical

Re: simple query running for ever

2020-06-16 Thread Justin Pryzby
On Tue, Jun 16, 2020 at 08:35:31PM +, Nagaraj Raj wrote: > I wrote a simple query, and it is taking too long, not sure what is wrong in > it, even its not giving EXPLAIN ANALYZE. > > select T0."physical_address_sid", T0."individual_entity_proxy_id", > T2."infrrd_hh_rank_nbr" > from "cms_pros

Re: simple query running for ever

2020-06-16 Thread Nagaraj Raj
And here is the explain analyze: https://explain.depesz.com/s/uQGA Thanks! On Tuesday, June 16, 2020, 02:13:37 PM PDT, Nagaraj Raj wrote: Hi Michael, Sorry, I missed table structure, explain select T0."physical_address_sid", T0."individual_entity_proxy_id", T2."infrrd_hh_rank_nb

Re: simple query running for ever

2020-06-16 Thread Nagaraj Raj
Hi Michael, Sorry, I missed table structure, explain select T0."physical_address_sid", T0."individual_entity_proxy_id", T2."infrrd_hh_rank_nbr" from "cms_prospects".PROSPECT T0 inner join public.t1680035748gcccqqdpmrblxp33_bkp T1 on T0."individual_entity_proxy_id" = T1."individual_entity_prox

Re: simple query running for ever

2020-06-16 Thread Nagaraj Raj
Hi Justin, My apologies, I missed that. Yes, I change work mem to 2GB but didn't see any difference. So, as your suggestion removed the distinct on pk and added a multi-column index so query planner did index-only can that is fixed the issue and query completed in 1Min. Best regards, Rj

Re: simple query running for ever

2020-06-16 Thread Justin Pryzby
On Tue, Jun 16, 2020 at 08:35:31PM +, Nagaraj Raj wrote: > I wrote a simple query, and it is taking too long, not sure what is wrong in > it, even its not giving EXPLAIN ANALYZE. Is this related to last week's question ? https://www.postgresql.org/message-id/1211705382.726951.1592174752720%40

Re: simple query running for ever

2020-06-16 Thread Michael Lewis
On Tue, Jun 16, 2020 at 2:35 PM Nagaraj Raj wrote: > I wrote a simple query, and it is taking too long, not sure what is wrong > in it, even its not giving EXPLAIN ANALYZE. > More context is needed. Please review- https://wiki.postgresql.org/wiki/Slow_Query_Questions

simple query running for ever

2020-06-16 Thread Nagaraj Raj
I wrote a simple query, and it is taking too long, not sure what is wrong in it, even its not giving EXPLAIN ANALYZE. select T0."physical_address_sid", T0."individual_entity_proxy_id", T2."infrrd_hh_rank_nbr" from "cms_prospects".PROSPECT T0 --inner join "sas_prs_tmp".DEDUPE3583E3F18 T1 on T0."

Re: view reading information_schema is slow in PostgreSQL 12

2020-06-16 Thread regrog
I tested both postgres 12.3 and 13 beta 1 and the results are the same. I could read the pg_ tables instead of the views in the information_schema but that's the SQL standard schema so I'd prefer to stick to that. I reported this issue because the performance gap is huge and that could be useful