Re: Enumeration of tables is very slow in largish database

On 01/12/2012 01:34 AM, Tom Lane wrote:
> =?ISO-8859-1?Q?Kirill_M=FCller?=<kirill(dot)mueller(at)ivt(dot)baug(dot)ethz(dot)ch> writes:
>> When leaving out the last two "AND NOT EXISTS..." parts, the query
>> finishes in no time. I have attached the output of EXPLAIN ANALYZE -- if
>> I understand the execution tree correctly, the time is burnt in repeated
>> sequential scans of the geometry_columns table (line 38).
> Yeah. It wouldn't choose that plan if it weren't for the horrid rowcount
> misestimate here:
>
>> -> Hash Anti Join (cost=30586.95..37075.41 rows=1 width=133) (actual time=945.911..1760.307 rows=17836 loops=1)

I have VACUUM ANALYZE-d the table just before testing. Seems that this
didn't help here.
>
> This is probably an indication of eqjoinsel_semi doing the wrong thing;
> we've whacked that estimator around a few times now, so it's hard to
> know whether this represents an already-fixed bug or not. What PG
> version are you using exactly?
muelleki(at)xxx:~$ psql
psql (8.4.8)
>
> Maybe use EXCEPT instead of a WHERE condition to get rid of the
> already-present entries?
Thank you for the hint, I was not aware of the rather elegant EXCEPT.
Using WHERE (..., ...) NOT IN (SELECT ..., ... FROM ...) as suggested by
David Johnston shows excellent performance (and better fits the code
that is generating the SQL), but I'll keep the EXCEPT option in mind.

Regards

Kirill