Evan Martin <postgresql@xxxxxxxxxxxxxxxxx> writes: > I've run into a weird query performance problem. I have a large, complex > query which joins the results of several set-returning functions with > some tables and filters them by calling another function, which involves > PostGIS calls (ST_DWithin). This used to run in about 10 seconds until I > changed the functions to allow them to be inlined. (They previously had > "SET search_path FROM current", which prevented inlining.) Now the query > doesn't return in 10 minutes! You didn't show EXPLAIN ANALYZE results, but I see that one query is estimating that 6667 rows from _test_pos pass the filter, while the other thinks only 1 row passes; that changes the planner's ideas about how to do the join, and evidently not for the better. In the case of the opaque user-defined function, you're just getting a default selectivity estimate, and it's really just blind luck if that is close to reality. But in the other case it should be invoking PostGIS-provided selectivity estimation functions, and apparently those are giving poor results. I think you'd be best off to ask about that on the PostGIS mailing lists. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
