To: kaloyan@digsys.bg
cc: pgsql-performance@postgresql.org
Subject: Re: Question about LEFT JOIN and query plan 
In-reply-to: <4C811F68.9060601@digsys.bg> 
References: <4C811F68.9060601@digsys.bg>
Comments: In-reply-to Kaloyan Iliev Iliev <kaloyan@digsys.bg>
	message dated "Fri, 03 Sep 2010 19:16:40 +0300"
Date: Fri, 03 Sep 2010 13:14:43 -0400
Message-ID: <25498.1283534083@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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