agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Justin Pryzby <pryzby@telsasoft.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query performance issue
Date: Sun, 14 Feb 2021 23:03:50 +0100
Message-ID: <9eff2cdd-bf22-562a-805e-95b30eda84d4@enterprisedb.com> (raw)
In-Reply-To: <20210122023514.GD27167@telsasoft.com>
References: <135856010.59446.1611280406074.ref@mail.yahoo.com>
<135856010.59446.1611280406074@mail.yahoo.com>
<20210122023514.GD27167@telsasoft.com>
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
view thread (57+ messages) latest in thread
Message-ID: <9eff2cdd-bf22-562a-805e-95b30eda84d4@enterprisedb.com>
Permalink: ../9eff2cdd-bf22-562a-805e-95b30eda84d4@enterprisedb.com/
Also on: postgresql.org/message-id/9eff2cdd-bf22-562a-805e-95b30eda84d4@enterprisedb.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@enterprisedb.com, pryzby@telsasoft.com, nagaraj.sf@yahoo.com, pgsql-performance@lists.postgresql.org
Subject: Re: Query performance issue
In-Reply-To: <9eff2cdd-bf22-562a-805e-95b30eda84d4@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox