Re: Hi guys, HELP please

On Fri, Sep 20, 2019 at 09:21:59PM +0000, Castillo, Steven (Agile)
wrote:
>Hi,
>
>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.

[1] https://wiki.postgresql.org/wiki/Slow_Query_Questions

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
magnitude).

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)

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services