Re: How to read query plan

From: Miroslav Šulc
Subject: Re: How to read query plan
Date: ,
Msg-id: 42349C96.60401@startnet.cz
(view: Whole thread, Raw)
In response to: Re: How to read query plan  (Tom Lane)
Responses: Re: How to read query plan  (Tom Lane)
List: pgsql-performance

Tree view

How to read query plan  (Miroslav Šulc, )
 Re: How to read query plan  (John Arbash Meinel, )
  Re: How to read query plan  (Miroslav Šulc, )
   Re: How to read query plan  (John Arbash Meinel, )
    Re: How to read query plan  (Miroslav Šulc, )
    Re: How to read query plan  (Tom Lane, )
     Re: How to read query plan  (Miroslav Šulc, )
      Re: How to read query plan  (Tom Lane, )
       Re: How to read query plan  (Miroslav Šulc, )
        Re: How to read query plan  (Tom Lane, )
   Re: How to read query plan  (John Arbash Meinel, )
    Re: How to read query plan  (Miroslav Šulc, )
     Re: How to read query plan  (John Arbash Meinel, )
      Re: How to read query plan  (Miroslav Šulc, )
      Re: How to read query plan  (Miroslav Šulc, )
       Re: How to read query plan  (Christopher Kings-Lynne, )
        Re: How to read query plan  (Miroslav Šulc, )
         Re: How to read query plan  (Greg Stark, )
       Re: How to read query plan  (PFC, )
        Re: How to read query plan  (Miroslav Šulc, )
         Re: How to read query plan  (Tom Lane, )
          Re: How to read query plan  (Miroslav Šulc, )
           Re: How to read query plan  (Kaloyan Iliev Iliev, )
            Re: How to read query plan  (Miroslav Šulc, )
         Re: How to read query plan  (John Arbash Meinel, )
 Re: How to read query plan  (Ragnar Hafstað, )
  Re: How to read query plan  (Miroslav Šulc, )
 Re: How to read query plan  (Tom Lane, )
  Re: [HACKERS] How to read query plan  (Tom Lane, )
   Re: [HACKERS] How to read query plan  (Miroslav Šulc, )
    Re: [HACKERS] How to read query plan  (Tom Lane, )
  Re: How to read query plan  (Miroslav Šulc, )
   Re: How to read query plan  (Tom Lane, )
    Re: How to read query plan  (Miroslav Šulc, )
   Re: How to read query plan  (John Arbash Meinel, )
 Re: How to read query plan  (Harald Fuchs, )
  Re: How to read query plan  (Miroslav Šulc, )
 Avoiding tuple construction/deconstruction during joining  (Tom Lane, )
  Re: Avoiding tuple construction/deconstruction during joining  (Miroslav Šulc, )
   Re: Avoiding tuple construction/deconstruction during joining  (Tom Lane, )
    Re: Avoiding tuple construction/deconstruction during joining  (Miroslav Šulc, )
  Re: Avoiding tuple construction/deconstruction during joining  (PFC, )
  Re: Avoiding tuple construction/deconstruction during joining  (PFC, )

Tom Lane wrote:

>=?windows-1250?Q?Miroslav_=8Aulc?= <> writes:
>
>
>>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
>>
>>
>
>Hmm.  Given the small size of the auxiliary tables, you'd think they'd
>fit in 1MB work_mem no problem.  But try bumping work_mem up to 10MB
>just to see if it makes a difference.  (BTW, you do know that altering
>the .conf file doesn't in itself do anything?  You have to SIGHUP the
>postmaster to make it notice the change ... and for certain parameters
>such as shared_buffers, you actually have to stop and restart the
>postmaster.  You can use the SHOW command to verify whether a change
>has taken effect.)
>
>
I've tried to set work_mem to 10240, restarted postmaster and tried the
EXPLAIN ANALYZE but there is only cca 200 ms speedup.

>>I have checked this and there are some JOINs smallint against integer.
>>Is that problem?
>>
>>
>That probably explains why some of the joins are merges instead of
>hashes --- hash join doesn't work across datatypes.  Doesn't seem like
>it should be a huge problem though.  I was more concerned about the
>possibility of slow locale-dependent string comparisons.
>
>
There are only JOINs number against number. I've tried to change one of
the fields from smallint to integer but there was no speedup.

>            regards, tom lane
>
>
Miroslav

Attachment

pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: How to read query plan
From: "Qingqing Zhou"
Date:
Subject: Re: One tuple per transaction