|From:||Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>|
|To:||"Castillo, Steven (Agile)" <Steven(dot)Castillo(at)umusic(dot)com>|
|Subject:||Re: Hi guys, HELP please|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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  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 http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
|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|