[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] inputs into query planner costing
From:       Mike Roest <mike.roest () replicon ! com>
Date:       2015-03-31 19:22:31
Message-ID: CAE7Byhiyj7j=rQgYxGaVqtRemraOVMsPCU7-t0oX4uxEB=PjwA () mail ! gmail ! com
[Download RAW message or body]

Thanks for the responses

For anyone searching in the future I'll answer Tom's questions and list the
boneheaded fix that it ended up actually being (really painful as I've been
fighting this for a week).

1) According to amazon they run stock postgres as far as the query planner
is concerned.
2) Yes sorry I forgot to note on our prod system the random_page_cost was 2
vs 4 on the RDS system.
3) I had run vacuum on all the tables in the query and the pg_relation_size
on the tables aren't way out of wack
4) Yep both default_statistics_target was the default of 100 on both.

I was concentrating completely on the wrong direction here.  What it turned
out to be was the RDS configuration of postgres which we had modified
somewhat I had missed configuring work_mem to something greater then their
default of 1 MB.  Once I brought work_mem upto the same value as our
production server low and behold the query runs fast.

Sorry for wasting everyones time.  Hopefully this will help someone else
down the line.

[Attachment #3 (text/html)]

<div dir="ltr">Thanks for the responses  <div class="gmail_extra"><br><div \
class="gmail_quote">For anyone searching in the future I&#39;ll answer Tom&#39;s \
questions and list the boneheaded fix that it ended up actually being (really painful \
as I&#39;ve been fighting this for a week).</div><div \
class="gmail_quote"><br></div><div class="gmail_quote">1) According to amazon they \
run stock postgres as far as the query planner is concerned.</div><div \
class="gmail_quote">2) Yes sorry I forgot to note on our prod system the \
random_page_cost was 2 vs 4 on the RDS system.</div><div class="gmail_quote">3) I had \
run vacuum on all the tables in the query and the pg_relation_size on the tables \
aren&#39;t way out of wack</div><div class="gmail_quote">4) Yep both \
default_statistics_target was the default of 100 on both.</div><div \
class="gmail_quote"><br></div><div class="gmail_quote">I was concentrating completely \
on the wrong direction here.   What it turned out to be was the RDS configuration of \
postgres which we had modified somewhat I had missed configuring work_mem to \
something greater then their default of 1 MB.   Once I brought work_mem upto the same \
value as our production server low and behold the query runs fast.</div><div \
class="gmail_quote"><br></div><div class="gmail_quote">Sorry for wasting everyones \
time.   Hopefully this will help someone else down the line.</div> </div></div>



[prev in list] [next in list] [prev in thread] [next in thread]