Re: How to read query plan

Tom Lane wrote:

>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.
>
>
I've just tried to uncomment the settings for these parameters with with
no impact on the query speed.

shared_buffers = 48000          # min 16, at least max_connections*2,
8KB each
work_mem = 1024         # min 64, size in KB
maintenance_work_mem = 16384    # min 1024, size in KB
max_stack_depth = 2048          # min 100, size in KB

>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?
>
>
I have checked this and there are some JOINs smallint against integer.
Is that problem? I would use smallint for IDPKs of some smaller tables
but the lack of SMALLSERIAL and my laziness made me use SERIAL instead
which is integer.

>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)?
>
>
I've already posted the hardware info. OS is Linux (Gentoo) with kernel
2.6.11.

>            regards, tom lane
>
>
Miroslav