Tom,
First off, I wouldn't use a VM if I could help it, however, sometimes you have
to make compromises. With a 16 Gb machine running 64-bit Ubuntu and only
PostgreSQL, I'd start by allocating 4 Gb to shared_buffers. That should leave
more than enough room for the OS and file system cache. T
On 10 June 2010 18:47, AI Rumman wrote:
> I am using Postgresql 8.1 and did not find FETCH_COUNT
>
>
Oh ok. Looks like FETCH_COUNT was introduced in 8.2
> On Thu, Jun 10, 2010 at 6:55 PM, Amit Khandekar <
> amit.khande...@enterprisedb.com> wrote:
>
>>
>>
>> On 10 June 2010 18:05, AI Rumman wro
Hi, Andy.
I assume you are doing this in a loop? Many Many Many times? cuz:
>
Yes. Here are the variations I have benchmarked (times are best of three):
Variation #0
-no date field-
Explain: http://explain.depesz.com/s/Y9R
Time: 2.2s
Variation #1
date('1960-1-1')
Explain: http://explain.depes
I changed random_page_cost=4 (earlier 2) and the performance issue is gone
I am not clear why a page_cost of 2 on really fast disks would perform badly.
Thank you for all your help and time.
On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu wrote:
> Attached
>
> Thank you
>
>
> On Thu, Jun 10, 2010 at 6
On 06/10/2010 07:41 PM, David Jarvis wrote:
Hi,
I found a slow part of the query:
SELECT
* date(extract(YEAR FROM m.taken)||'-1-1') d1,*
* date(extract(YEAR FROM m.taken)||'-1-31') d2*
FROM
climate.city c,
climate.station s,
climate.station_category sc,
climate.measurement m
WHERE
Joe Conway wrote:
On 06/10/2010 01:21 PM, Anne Rosset wrote:
I tried that and it didn't make any difference. Same query plan.
A little experimentation suggests this might work:
create index item_rank_project on item_rank(project_id, rank) where
pf_id IS NULL;
Joe
Yes i
On 06/10/2010 01:21 PM, Anne Rosset wrote:
>>
> I tried that and it didn't make any difference. Same query plan.
A little experimentation suggests this might work:
create index item_rank_project on item_rank(project_id, rank) where
pf_id IS NULL;
Joe
signature.asc
Description: OpenPGP digi
On 10/06/10 23:08, Anne Rosset wrote:
Heikki Linnakangas wrote:
On 10/06/10 22:47, Craig James wrote:
Postgres normally doesn't index NULL values even if the column is
indexed, so it has to do a table scan when your query includes an IS
NULL condition.
That was addressed in version 8.3. 8.3 a
On Thu, Jun 10, 2010 at 12:58 PM, Anj Adu wrote:
> you are right..the word "zone" was replaced by "area" (my bad )
>
> everything else is as is.
>
> Apologies for the confusion.
Well, two different people have asked you for the table and index
definitions now, and you haven't provided them... I t
Hi,
I found a slow part of the query:
SELECT
* date(extract(YEAR FROM m.taken)||'-1-1') d1,*
* date(extract(YEAR FROM m.taken)||'-1-31') d2*
FROM
climate.city c,
climate.station s,
climate.station_category sc,
climate.measurement m
WHERE
c.id = 5148 AND ...
Date extraction is 3.2 se
Joe Conway wrote:
On 06/10/2010 01:10 PM, Joe Conway wrote:
try:
create index item_rank_null_idx on item_rank(pf_id)
where rank IS NOT NULL AND pf_id IS NULL;
oops -- that probably should be:
create index item_rank_null_idx on item_rank(project_id)
where rank IS NOT NULL AND pf_id IS
On 06/10/2010 01:10 PM, Joe Conway wrote:
> try:
>
> create index item_rank_null_idx on item_rank(pf_id)
> where rank IS NOT NULL AND pf_id IS NULL;
oops -- that probably should be:
create index item_rank_null_idx on item_rank(project_id)
where rank IS NOT NULL AND pf_id IS NULL;
Joe
signatu
On 10/06/10 22:47, Craig James wrote:
Postgres normally doesn't index NULL values even if the column is
indexed, so it has to do a table scan when your query includes an IS
NULL condition.
That was addressed in version 8.3. 8.3 and upwards can use an index for
IS NULL.
I believe the NULLs we
Craig James wrote:
On 6/10/10 12:34 PM, Anne Rosset wrote:
Jochen Erwied wrote:
Thursday, June 10, 2010, 8:36:08 PM you wrote:
psrdb=# (SELECT
psrdb(# MAX(item_rank.rank) AS maxRank
psrdb(# FROM
psrdb(# item_rank item_rank
psrdb(# WHERE
psrdb(# item_rank.project_id='proj2783'
psrdb(# AND item
On 6/10/10 12:34 PM, Anne Rosset wrote:
Jochen Erwied wrote:
Thursday, June 10, 2010, 8:36:08 PM you wrote:
psrdb=# (SELECT
psrdb(# MAX(item_rank.rank) AS maxRank
psrdb(# FROM
psrdb(# item_rank item_rank
psrdb(# WHERE
psrdb(# item_rank.project_id='proj2783'
psrdb(# AND item_rank.pf_id IS NULL
Thursday, June 10, 2010, 8:36:08 PM you wrote:
> psrdb=# (SELECT
> psrdb(#MAX(item_rank.rank) AS maxRank
> psrdb(# FROM
> psrdb(#item_rank item_rank
> psrdb(# WHERE
> psrdb(#item_rank.project_id='proj2783'
> psrdb(# AND item_rank.pf_id IS NULL
> psrdb(#
> psrdb(# )
Max Williams wrote:
Can I just turn this off on 8.4.4 or is it a compile time option
You can update your postgresql.conf to include:
debug_assertions = false
And restart the server. This will buy you back *some* of the
performance loss but not all of it. Will have to wait for corrected
pa
On 06/10/2010 12:56 PM, Anne Rosset wrote:
> Craig James wrote:
>> create index item_rank_null_idx on item_rank(pf_id)
>>where item_rank.pf_id is null;
>>
>> Craig
>>
> Hi Craig,
> I tried again after adding your suggested index but I didn't see any
> improvements: (seems that the index is no
Thursday, June 10, 2010, 9:34:07 PM you wrote:
> Time: 1.516 ms
> Time: 13.177 ms
I'd suppose the first query to scan a lot less rows than the second one.
Could you supply an explained plan for the fast query?
--
Jochen Erwied | home: joc...@erwied.eu +49-208-38800-18, FAX: -19
Saue
Kenneth Marshall wrote:
On Thu, Jun 10, 2010 at 12:34:07PM -0700, Anne Rosset wrote:
Jochen Erwied wrote:
Thursday, June 10, 2010, 8:36:08 PM you wrote:
psrdb=# (SELECT
psrdb(#MAX(item_rank.rank) AS maxRank
psrdb(# FROM
psrdb(#item_rank item_rank
psrdb(# WHER
On Thu, Jun 10, 2010 at 12:34:07PM -0700, Anne Rosset wrote:
> Jochen Erwied wrote:
>> Thursday, June 10, 2010, 8:36:08 PM you wrote:
>>
>>
>>> psrdb=# (SELECT
>>> psrdb(#MAX(item_rank.rank) AS maxRank
>>> psrdb(# FROM
>>> psrdb(#item_rank item_rank
>>> psrdb(# WHERE
>>> psrdb(#
Heikki Linnakangas wrote:
On 10/06/10 22:47, Craig James wrote:
Postgres normally doesn't index NULL values even if the column is
indexed, so it has to do a table scan when your query includes an IS
NULL condition.
That was addressed in version 8.3. 8.3 and upwards can use an index
for IS NUL
Jochen Erwied wrote:
Thursday, June 10, 2010, 9:34:07 PM you wrote:
Time: 1.516 ms
Time: 13.177 ms
I'd suppose the first query to scan a lot less rows than the second one.
Could you supply an explained plan for the fast query?
Hi Jochen,
Here is the explained plan fo
Jochen Erwied wrote:
Thursday, June 10, 2010, 8:36:08 PM you wrote:
psrdb=# (SELECT
psrdb(#MAX(item_rank.rank) AS maxRank
psrdb(# FROM
psrdb(#item_rank item_rank
psrdb(# WHERE
psrdb(#item_rank.project_id='proj2783'
psrdb(# AND item_rank.pf_id IS NULL
psrdb(#
p
Jesper Krogh wrote:
On 2010-06-10 19:50, Anne Rosset wrote:
Any advice on how to make it run faster?
What timing do you get if you run it with \t (timing on) and without
explain analyze ?
I would be surprised if you can get it much faster than what is is.. I
may be that a
significant porti
On Thu, Jun 10, 2010 at 10:50:40AM -0700, Anne Rosset wrote:
> Any advice on how to make it run faster?
First, let me ask a simple question - what runtime for this query will
be satisfactory for you?
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depes
On 2010-06-10 19:50, Anne Rosset wrote:
Any advice on how to make it run faster?
What timing do you get if you run it with \t (timing on) and without
explain analyze ?
I would be surprised if you can get it much faster than what is is.. I
may be that a
significant portion is "planning cost"
Hi,
I have the following query that needs tuning:
psrdb=# explain analyze (SELECT
psrdb(#MAX(item_rank.rank) AS maxRank
psrdb(# FROM
psrdb(#item_rank item_rank
psrdb(# WHERE
psrdb(#item_rank.project_id='proj2783'
psrdb(# AND item_rank.pf_id IS NULL
psrdb(#
psrdb(#
you are right..the word "zone" was replaced by "area" (my bad )
everything else is as is.
Apologies for the confusion.
On Thu, Jun 10, 2010 at 9:42 AM, Robert Haas wrote:
> On Thu, Jun 10, 2010 at 11:32 AM, Anj Adu wrote:
>> Attached
>
> Hmm. Well, I'm not quite sure what's going on here, but
On Fri, Jun 4, 2010 at 12:40 AM, Yogesh Naik
wrote:
> I am performing a DB insertion and update for 3000+ records and while doing
> so i get CPU utilization
> to 100% with 67% of CPU used by postgres
That sounds normal to me. What would you expect to happen?
--
Robert Haas
EnterpriseDB: htt
On Thu, Jun 10, 2010 at 11:32 AM, Anj Adu wrote:
> Attached
Hmm. Well, I'm not quite sure what's going on here, but I think you
must be using a modified verison of PostgreSQL, because, as Tom
pointed out upthread, we don't have a data type called "timestamp with
time area". It would be called "
Excerpts from Tom Lane's message of jue jun 10 11:46:25 -0400 2010:
> Yes, the folks at commandprompt need to be told about this. Loudly.
> It's a serious packaging error.
Just notified Lacey, the packager (not so loudly, though); she's working
on new packages, and apologizes for the inconvenien
Max Williams writes:
> Ah, yes its OFF for 8.4.3 and ON for 8.4.4!
Hah.
> Can I just turn this off on 8.4.4 or is it a compile time option?
Well, you can turn it off, but that will only buy back part of the
cost (and not even the bigger part, I believe).
> Also is this a mistake or intended? P
Ah, yes its OFF for 8.4.3 and ON for 8.4.4!
Can I just turn this off on 8.4.4 or is it a compile time option?
Also is this a mistake or intended? Perhaps I should tell the person who builds
the pgdg packages??
Cheers,
Max
-Original Message-
From: Kevin Grittner [mailto:kevin.gritt...@w
Attached
Thank you
On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas wrote:
> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu wrote:
>> The plan is unaltered . There is a separate index on theDate as well
>> as one on node_id
>>
>> I have not specifically disabled sequential scans.
>
> Please do "SHOW ALL
Please keep the mailing list CC'd, so that others can help.
On 10/06/10 15:30, Ambarish Bhattacharya wrote:
On 10/06/10 11:47, Ambarish Bhattacharya wrote:
It would be helpful if you can suggest me the appropriate Autovacuum settings
for handling this large data as my autovacuum setting is han
Max Williams writes:
> I'm afraid pg_config is not part of the pgdg packages.
Sure it is. They might've put it in the -devel subpackage, though.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subs
Max Williams wrote:
> I'm afraid pg_config is not part of the pgdg packages.
Connect (using psql or your favorite client) and run:
show debug_assertions;
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.
I'm afraid pg_config is not part of the pgdg packages.
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: 10 June 2010 15:11
To: Max Williams
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to
8.4.4?
Max
True, plus there are the other issues of increased checkpoint times and I/O,
bgwriter tuning, etc. It may be better to let the OS cache the files and size
shared_buffers to a smaller value.
Bob Lunney
--- On Wed, 6/9/10, Robert Haas wrote:
> From: Robert Haas
> Subject: Re: [PERFORM] requ
Max Williams writes:
> How do I tell if it was built with debugging options?
Run pg_config --configure and see if --enable-cassert is mentioned.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subsc
On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu wrote:
> The plan is unaltered . There is a separate index on theDate as well
> as one on node_id
>
> I have not specifically disabled sequential scans.
Please do "SHOW ALL" and attach the results as a text file.
> This query performs much better on 8.1.9
I am using Postgresql 8.1 and did not find FETCH_COUNT
On Thu, Jun 10, 2010 at 6:55 PM, Amit Khandekar <
amit.khande...@enterprisedb.com> wrote:
>
>
> On 10 June 2010 18:05, AI Rumman wrote:
>
>> Could you please give me the link for cursor- How to use it?
>>
>>
>> On Thu, Jun 10, 2010 at 6:28 P
On 10 June 2010 18:05, AI Rumman wrote:
> Could you please give me the link for cursor- How to use it?
>
>
> On Thu, Jun 10, 2010 at 6:28 PM, Kevin Grittner <
> kevin.gritt...@wicourts.gov> wrote:
>
>> AI Rumman wrote:
>>
>> >> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
>>
Could you please give me the link for cursor- How to use it?
On Thu, Jun 10, 2010 at 6:28 PM, Kevin Grittner wrote:
> AI Rumman wrote:
>
> >> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
>
> > And the query does not return data though I have been waiting for
> > 10 mins.
> >
AI Rumman wrote:
>> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
> And the query does not return data though I have been waiting for
> 10 mins.
>
> Do you have any idea ?
Unless you use a cursor, PostgreSQL interfaces typically don't show
any response on the client side u
2010/6/10 AI Rumman
> I found only AccessShareLock in pg_locks during the query.
> And the query does not return data though I have been waiting for 10 mins.
>
> Do you have any idea ?
>
>
> On Thu, Jun 10, 2010 at 5:26 PM, Szymon Guz wrote:
>
>>
>>
>> 2010/6/10 AI Rumman
>>
>> Can anyone pleas
I found only AccessShareLock in pg_locks during the query.
And the query does not return data though I have been waiting for 10 mins.
Do you have any idea ?
On Thu, Jun 10, 2010 at 5:26 PM, Szymon Guz wrote:
>
>
> 2010/6/10 AI Rumman
>
> Can anyone please tell me why the following query hangs?
2010/6/10 AI Rumman
> Can anyone please tell me why the following query hangs?
> This is a part of a large query.
>
> explain
> select *
> from vtiger_emaildetails
> inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid =
> vtiger_vantage_email_track.mailid
> left join vtiger_seact
How do I tell if it was built with debugging options?
-Original Message-
From: Devrim GÜNDÜZ [mailto:dev...@gunduz.org]
Sent: 10 June 2010 09:30
To: Robert Haas
Cc: Max Williams; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Large (almost 50%!) performance drop after upgrading
Can anyone please tell me why the following query hangs?
This is a part of a large query.
explain
select *
from vtiger_emaildetails
inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid =
vtiger_vantage_email_track.mailid
left join vtiger_seactivityrel on vtiger_seactivityrel.activi
On 10/06/10 11:47, Ambarish Bhattacharya wrote:
It would be helpful if you can suggest me the appropriate Autovacuum settings
for handling this large data as my autovacuum setting is hanging the entire
process.
What do you mean by "hanging the entire process"?
--
Heikki Linnakangas
Enter
Dear Experts,
I have data about half milllion to 1 million which is populated into the
Postgres db using a batch job (A sql script consists of pl/pgsql functions and
views) .
I am using PostgreSQL 8.3.5 on windows 2003 64-Bit machine.
It would be helpful if you can suggest me the app
On Wed, 2010-06-09 at 21:51 -0400, Robert Haas wrote:
> On Wed, Jun 9, 2010 at 6:56 AM, Max Williams
> wrote:
> > Any input? I can reproduce these numbers consistently. If you need
> more
> > information then just let me know. By the way, I am a new postgresql
> user so
> > my experience is limite
Well the packages are from the pgdg repo which I would have thought are pretty
common?
https://public.commandprompt.com/projects/pgcore/wiki
-Original Message-
From: Robert Haas [mailto:robertmh...@gmail.com]
Sent: 10 June 2010 02:52
To: Max Williams
Cc: pgsql-performance@postgresql.org
55 matches
Mail list logo
