Re: Inefficient query plan

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: Inefficient query plan
Дата
Msg-id 4C7232520200002500034A48@gw.wicourts.gov
обсуждение исходный текст
Ответ на Re: Inefficient query plan  (Jann Röder <roederja@ethz.ch>)
Ответы Re: Inefficient query plan  (Jann Röder <roederja@ethz.ch>)
Список pgsql-performance
Jann Röder<roederja@ethz.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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Grzegorz Jaśkiewicz
Дата:
Сообщение: Re: Inefficient query plan
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Inefficient query plan