Yeah.. I came across pgtune but noticed that latest version dated 2009-10-29
http://pgfoundry.org/frs/?group_id=1000416 which is kind of outdated. Tar
file has settings for pg 8.3. Is still relevant?
Yes, I'm sure it will not do anything bad to your config.
Apologies for leaping in a
On Monday, January 28, 2013, Alex Vinnik wrote:
It sure turned out that default settings are not a good fit. Setting
random_page_cost
to 1.0 made query to run in 2.6 seconds and I clearly see that indexes are
being used in explain plan and IO utilization is close to 0.
This is not
On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski plk.zu...@gmail.comwrote:
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik alvinni...@gmail.com wrote:
It sure turned out that default settings are not a good fit.
do you know pgtune?
it's a good tool for starters, if you want a fast
Setting work_mem to 64MB triggers in memory sort but look what happens with
views look up. PG goes through all records there Seq Scan on views
instead of using visitor_id index and I have only subset of real data to
play around. Can imagine what cost would be running it against bigger
dataset.
On Tue, Jan 29, 2013 at 8:24 AM, Alex Vinnik alvinni...@gmail.com wrote:
On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski
plk.zu...@gmail.comwrote:
do you know pgtune?
it's a good tool for starters, if you want a fast postgres and don't
really want to learn what's behind the scenes.
On Tue, Jan 29, 2013 at 8:41 AM, Alex Vinnik alvinni...@gmail.com wrote:
Setting work_mem to 64MB triggers in memory sort but look what happens with
views look up. PG goes through all records there Seq Scan on views instead
of using visitor_id index and I have only subset of real data to play
On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
random_page_cost=1 might be not what you really want.
it would mean that random reads are as fast as as sequential reads, which
probably is true only for SSD
What randon_page_cost would be more appropriate for EC2 EBS Provisioned
volume that
On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot be...@silentmedia.com wrote:
On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
random_page_cost=1 might be not what you really want.
it would mean that random reads are as fast as as sequential reads, which
probably is true only for SSD
What
On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik alvinni...@gmail.com wrote:
On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot be...@silentmedia.com wrote:
On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
random_page_cost=1 might be not what you really want.
it would mean that random reads are as
On Mon, Jan 28, 2013 at 3:43 PM, Alex Vinnik alvinni...@gmail.com wrote:
It sure turned out that default settings are not a good fit. Setting
random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly see
that indexes are being used in explain plan and IO utilization is close to
0.
On Tue, Jan 29, 2013 at 2:06 PM, Jeff Janes jeff.ja...@gmail.com wrote:
Sort Key: visits.id, views.id
Sort Method: external sort Disk: 4248kB
What query are you running? The query you originally showed us should
not be doing this sort in the first place.
Cheers,
Jeff
Here is
index definition
CREATE INDEX views_visit_id_visit_buoy_index ON views USING btree
(visit_id, visit_buoy)
On Tue, Jan 29, 2013 at 1:35 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik alvinni...@gmail.com
wrote:
On Tue, Jan 29, 2013 at 11:39
On Mon, Jan 28, 2013 at 4:55 PM, Filip Rembiałkowski
plk.zu...@gmail.com wrote:
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik alvinni...@gmail.com wrote:
It sure turned out that default settings are not a good fit.
do you know pgtune?
it's a good tool for starters, if you want a fast
It sure turned out that default settings are not a good fit. Setting
random_page_cost
to 1.0 made query to run in 2.6 seconds and I clearly see that indexes are
being used in explain plan and IO utilization is close to 0.
QUERY PLAN
Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik alvinni...@gmail.com wrote:
It sure turned out that default settings are not a good fit.
do you know pgtune?
it's a good tool for starters, if you want a fast postgres and don't really
want to learn what's behind the scenes.
random_page_cost=1
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik alvinni...@gmail.com wrote:
It sure turned out that default settings are not a good fit. Setting
random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly see
that indexes are being used in explain plan and IO utilization is close to
0.
Guys, thanks a lot for your input. It is very valuable for us. We plan to
fix a separate dev server similar to production one, copy all data there
and try you suggestions as we really don't want to do it on production
server. I also noticed that IOPS jumps to 100% when running this query. So
it is
On Wed, Jan 9, 2013 at 9:49 AM, Alex Vinnik alvinni...@gmail.com wrote:
Guys, thanks a lot for your input. It is very valuable for us. We plan to
fix a separate dev server similar to production one, copy all data there and
try you suggestions as we really don't want to do it on production
On Thursday, January 3, 2013, Alex Vinnik wrote:
Hi everybody,
I have implemented my first app using PG DB and thought for a minute(may
be two) that I know something about PG but below
problem totally destroyed my confidence :). Please help me to restore it.
Here is simple join query. It
On Thu, Jan 3, 2013 at 4:54 PM, Alex Vinnik alvinni...@gmail.com wrote:
Don't understand why PG doesn't use views_visit_id_index in that query but
rather scans whole table. One explanation I have found that when resulting
dataset constitutes ~15% of total number of rows in the table then seq
Hi everybody,
I have implemented my first app using PG DB and thought for a minute(may be
two) that I know something about PG but below problem totally destroyed my
confidence :). Please help me to restore it.
Here is simple join query. It runs just fine on MS SQL 2008 and uses
all available
On 01/03/2013 10:54 PM, Alex Vinnik wrote:
I have implemented my first app using PG DB and thought for a minute(may be
two) that I know something about PG but below problem totally destroyed my
confidence :). Please help me to restore it.
https://wiki.postgresql.org/wiki/SlowQueryQuestions
--
On 01/03/2013 11:54 PM, Alex Vinnik wrote:
Don't understand why PG doesn't use views_visit_id_index in that query
but rather scans whole table. One explanation I have found that when
resulting dataset constitutes ~15% of total number of rows in the table
then seq scan is used. In this case
23 matches
Mail list logo
