John Arbash Meinel <john@arbash-meinel.com> writes:
> How about a quick side track.
> Have you played around with your shared_buffers, maintenance_work_mem,
> and work_mem settings?
Indeed. The hash joins seem unreasonably slow considering how little
data they are processing (unless this is being run on some ancient
toaster...). One thought that comes to mind is that work_mem may be
set so small that the hashes are forced into multiple batches.
Another question worth asking is what are the data types of the columns
being joined on. If they are character types, what locale and encoding
is the database using?
> Are you re-running the query multiple times, and reporting the later
> speeds, or just the first time? (If nothing is loaded into memory, the
> first run is easily 10x slower than later ones.)
That cost would be paid during the bottom-level scans though. The thing
that strikes me here is that nearly all of the cost is being spent
joining.
> What version of postgres are you using?
And what's the platform (hardware and OS)?
regards, tom lane