On 01/12/2012 01:34 AM, Tom Lane wrote:
=?ISO-8859-1?Q?Kirill_M=FCller?=<kirill.mueller@xxxxxxxxxxxxxxxx> 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@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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
