From: Tom Lane <tgl@sss.pgh.pa.us>
To: kaloyan@digsys.bg
Cc: pgsql-performance@postgresql.org
Subject: Re: Question about LEFT JOIN and query plan
Date: Fri, 03 Sep 2010 13:14:43 -0400
Message-ID: <25498.1283534083@sss.pgh.pa.us> (raw)
In-Reply-To: <4C811F68.9060601@digsys.bg>
References: <4C811F68.9060601@digsys.bg>
Kaloyan Iliev Iliev <kaloyan@digsys.bg> writes:
> I have I query which behave strangely (according to me).
> According to the first plan PG makes absolutely unnecessary seq scan on
> tables "invoices" and "domeini" and etc.
I think you might get better results if you could get this rowcount
estimate a bit more in line with reality:
> -> Seq Scan on debts_desc dd (cost=0.00..2866.52 rows=23782 width=46) (actual time=0.481..45.085 rows=1037 loops=1)
> Filter: (active AND (NOT paid) AND has_proform AND (NOT storned))
It's choosing to hash instead of doing (what it thinks will be) 23K
index probes into the other table. For 1000 probes the decision
might be different.
I don't know if raising the stats target for that table will be enough
to fix it. Most likely those four conditions are not uncorrelated.
You might need to think about revising the table's representation
so that the query condition can be simpler and thus more accurately
estimated.
regards, tom lane
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: tgl@sss.pgh.pa.us, kaloyan@digsys.bg
Subject: Re: Question about LEFT JOIN and query plan
In-Reply-To: <25498.1283534083@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox