> 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
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 surp
On Mon, Jan 28, 2013 at 4:55 PM, Filip Rembiałkowski
wrote:
>
> On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik 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
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 wrote:
> On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik
> wrote:
> >
> >
> >
> > On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot
> wrote:
> >>
On Tue, Jan 29, 2013 at 2:06 PM, Jeff Janes 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 the query
On Mon, Jan 28, 2013 at 3:43 PM, 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.
>
> QUERY PLAN
>
On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik wrote:
>
>
>
> On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot 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 read
On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot 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 randon_page_cost
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
On Tue, Jan 29, 2013 at 8:41 AM, Alex Vinnik 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
> around. Can ima
On Tue, Jan 29, 2013 at 8:24 AM, Alex Vinnik wrote:
> On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski
> wrote:
>
>>
>> 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.
>>
> Yeah.. I came across pgtu
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. So
On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski wrote:
>
> On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik 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
On Mon, Jan 28, 2013 at 5:43 PM, 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.
>
> QUERY PLAN
>
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik 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 might be not what you r
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
tim
On Wed, Jan 9, 2013 at 9:49 AM, Alex Vinnik 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 server. I
> also notice
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 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
On Thu, Jan 3, 2013 at 4: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 t
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 result
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
--
J
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 inde
23 matches
Mail list logo
