Skip site navigation (1) Skip section navigation (2)

Re: Inefficient query plan

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: Jann Röder <roederja(at)ethz(dot)ch>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Inefficient query plan
Date: 2010-08-23 13:33:22
Message-ID: 4C7232520200002500034A48@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Jann Röder<roederja(at)ethz(dot)ch> wrote:
 
>                  Table "public.papers"
>       Column      |          Type           | Modifiers
> ------------------+-------------------------+-----------
>  itemid           | character(15)           | not null
 
> wos-db=> \d PaperReferences
>              Table "public.paperreferences"
>        Column       |         Type          | Modifiers
> --------------------+-----------------------+-----------
>  itemid             | character varying(15) | not null
 
> I just noticed that PaperReferences uses character varying (15)
> and Papers uses character(15). Stupid mistake of mine. Do you
> think this might cause the bad query planning?
 
Absolutely.  These are *not* the same type and don't compare all
that well.
 
> I will alter the table to use character(15) in both cases and see
> if that helps.
 
I suspect that making them the same will cure the problem, but I
would recommend you make any character(n) columns character
varying(n) instead of the other way around.  The the character(n)
data type has many surprising behaviors and tends to perform worse. 
Avoid using it if possible.
 
> postgresql.conf:
> max_connections = 20			
> shared_buffers = 256MB			
> work_mem = 10MB				
> maintenance_work_mem = 128MB		
> max_stack_depth = 4MB			
> synchronous_commit = off		
> wal_buffers = 1MB			
> checkpoint_segments = 10		
> effective_cache_size = 768MB
> default_statistics_target = 200	
> datestyle = 'iso, mdy'
> lc_messages = 'C'			
> lc_monetary = 'C'			
> lc_numeric = 'C'			
> lc_time = 'C'				
> default_text_search_config = 'pg_catalog.simple'
 
> Do you need an EXPLAIN ANALYZE output? Since it takes so long I
> can't easily post one right now. But maybe I can get one over
> night.
 
Not necessary; you've already identified the cause and the fix.
 
> My Hardware is an iMac running OS X 10.6.4 with 1.5 GB RAM and a
> 2.1 GHz (or so) core 2 Duo processor.
 
OK.  If you still don't get a good plan, you might want to try
edging up effective_cache_size, if the sum of your shared_buffers
and OS cache is larger than 768MB (which I would expect it might
be).  If the active part of your database (the part which is
frequently referenced) fits within cache space, or even a
significant portion of it fits, you might need to adjust
random_page_cost and perhaps seq_page_cost to reflect the lower
average cost of fetching from cache rather than disk -- but you want
to fix your big problem (the type mismatch) first, and then see if
you need further adjustments.
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-08-23 13:47:25
Subject: Re: Inefficient query plan
Previous:From: Grzegorz JaśkiewiczDate: 2010-08-23 13:28:51
Subject: Re: Inefficient query plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group