Subject: Re: Query performance issue
To: Justin Pryzby <pryzby@telsasoft.com>, Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: pgsql-performance@lists.postgresql.org
References: <135856010.59446.1611280406074.ref@mail.yahoo.com>
 <135856010.59446.1611280406074@mail.yahoo.com>
 <20210122023514.GD27167@telsasoft.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <9eff2cdd-bf22-562a-805e-95b30eda84d4@enterprisedb.com>
Date: Sun, 14 Feb 2021 23:03:50 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.6.0
MIME-Version: 1.0
In-Reply-To: <20210122023514.GD27167@telsasoft.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk


On 1/22/21 3:35 AM, Justin Pryzby wrote:
> On Fri, Jan 22, 2021 at 01:53:26AM +0000, Nagaraj Raj wrote:
>> Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle
>> Postgres 11 | db<>fiddle
>> Server configuration is: Version: 10.11RAM - 320GBvCPU - 32 "maintenance_work_mem" 256MB"work_mem"             1GB"shared_buffers" 64GB
> 
>> Aggregate  (cost=31.54..31.55 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=1)
>>    ->  Nested Loop  (cost=0.00..31.54 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=1)
>>          Join Filter: (a.household_entity_proxy_id = c.household_entity_proxy_id)
>>          ->  Nested Loop  (cost=0.00..21.36 rows=1 width=16) (actual time=0.006..0.007 rows=0 loops=1)
>>                Join Filter: (a.individual_entity_proxy_id = b.individual_entity_proxy_id)
>>                ->  Seq Scan on prospect a  (cost=0.00..10.82 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1)
>>                      Filter: (((last_contacted_anychannel_dttm IS NULL) OR (last_contacted_anychannel_dttm < '2020-11-23 00:00:00'::timestamp without time zone)) AND (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))
>>                ->  Seq Scan on individual_demographic b  (cost=0.00..10.53 rows=1 width=8) (never executed)
>>                      Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 'Prospect'::text))
>>          ->  Seq Scan on household_demographic c  (cost=0.00..10.14 rows=3 width=8) (never executed)
>>                Filter: (((hspnc_lang_prfrnc_cval)::text = ANY ('{B,E,X}'::text[])) OR (hspnc_lang_prfrnc_cval IS NULL))
>> Planning Time: 1.384 ms
>> Execution Time: 0.206 ms
>> 13 rows
> 
> It's doing nested loops with estimated rowcount=1, which indicates a bad
> underestimate, and suggests that the conditions are redundant or correlated.
> 

No, it's not. The dbfiddle does that because it's using empty tables, 
but the plan shared by Nagaraj does not contain any nested loops.

Nagaraj, if the EXPLAIN ANALYZE does not complete, there are two things 
you can do to determine which part of the plan is causing trouble.

Firstly, you can profile the backend using perf or some other profiles, 
and if we're lucky the function will give us some hints about which node 
type is using the CPU.

Secondly, you can "cut" the query into smaller parts, to run only parts 
of the plan - essentially start from inner-most join, and incrementally 
add more and more tables until it gets too long.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company