while you weren't looking, Derek Buttineau|Compu-SOLVE wrote:
> I'm hoping this is the right place to send this.
The PostgreSQL Performance list, pgsql-performance@postgresql.org
would be more appropriate. I'm copying my followup there, as well.
As for your query, almost all the time is actually spent in the
nestloop, not the sort. Compare:
> -> Sort (cost=31402.85..31405.06 rows=886 width=306) (actual
> time=87454.187..87454.240 rows=10 loops=1)
vs.
> -> Nested Loop (cost=0.00..31359.47 rows=886 width=306)
> (actual time=4.740..86430.468 rows=26308 loops=1)
That's 50-ish ms versus 80-odd seconds.
It seems to me a merge join might be more appropriate here than a
nestloop. What's your work_mem set at? Off-the-cuff numbers show the
dataset weighing in the sub-ten mbyte range.
Provided it's not already at least that big, and you don't want to up
it permanently, try saying:
SET work_mem = 10240; -- 10 mbytes
immediately before running this query (uncached, of course) and see
what happens.
Also, your row-count estimates look pretty off-base. When were these
tables last VACUUMed or ANALYZEd?
/rls
--
:wq