Re: Hi guys, HELP please

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: "Castillo, Steven (Agile)" <Steven(dot)Castillo(at)umusic(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Hi guys, HELP please
Date: 2019-09-23 11:42:17
Message-ID: 20190923114217.ttuqkctpsmzsjt67@development
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Fri, Sep 20, 2019 at 09:21:59PM +0000, Castillo, Steven (Agile)
>I wonder if you guys can help me with this, I've been struggling with
>this query for almost a week and I haven't been able to tune it, it
>runs forever and I need it to run fast.

Hard to say, because all we have is an explain without any additional
information (like amount of data, PostgreSQL version, settings like
work_mem). Maybe look at [1] which explains what to try, and also what
to include in your question.


Now, if I had to guess, I'd say this is a case of underestimate, causing
a choice of nested loops. That's fairly deadly.

In particular, I'm talking about this:

-> Seq Scan on t_territory_common tc (cost=0.00..6494012.54 rows=49 width=232)
Filter: (((source)::text = 'DSCHED'::text) AND ... many conditions ....

How many rows does this return when you query just this table (with all
the conditions)? Chances are those conditions are correlated, in which
case the number of rows is much higher than 49 (possibly by orders of

If that's the case, you have multiple options:

1) create a temporary table, and then joining it (can be analyzed,
estimates are likely much better)

2) disable nested loops for this query (useful for testing/investigation)

3) create extended statistics on those correlated columns (depends on
which PostgreSQL version you use)

4) redo the table schema (e.g. have a special column representing
combination of those columns), so that there's just a single condition
(thus no misestimate due to correlation)


Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-09-23 11:45:14 Re: PATCH: standby crashed when replay block which truncated in standby but failed to truncate in master node
Previous Message Antonin Houska 2019-09-23 10:44:31 Re: Attempt to consolidate reading of XLOG page