From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Evan Martin <postgresql(at)realityexists(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Slow queries when functions are inlined |
Date: | 2012-05-16 14:31:38 |
Message-ID: | 25162.1337178698@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Evan Martin <postgresql(at)realityexists(dot)net> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Evan Martin | 2012-05-16 14:48:03 | Re: Slow queries when functions are inlined |
Previous Message | Tom Lane | 2012-05-16 14:15:45 | Re: maybe incorrect regexp_replace behavior in v8.3.4 ? |