Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
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 Subject: Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4? On Wed, Jun 9, 2010 at 6:56 AM, Max Williams max.willi...@mflow.com 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 limited. Maybe different compile options? If we'd really slowed things down by 50% between 8.4.3 and 8.4.4, there'd be an awful lot of people screaming about it... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
On Wed, 2010-06-09 at 21:51 -0400, Robert Haas wrote: On Wed, Jun 9, 2010 at 6:56 AM, Max Williams max.willi...@mflow.com 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 limited. Maybe different compile options? If we'd really slowed things down by 50% between 8.4.3 and 8.4.4, there'd be an awful lot of people screaming about it... Given that there are 2 recent reports on the same issue, I wonder if the new packages were built with debugging options or not. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[PERFORM] Autovaccum settings while Bulk Loading data
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 appropriate Autovacuum settings for handling this large data as my autovacuum setting is hanging the entire process. As of now I have the below Autovacuum settings in postgresql.conf file. #-- # AUTOVACUUM PARAMETERS #-- autovacuum = on log_autovacuum_min_duration = 0 autovacuum_max_workers = 5 autovacuum_naptime = 10min autovacuum_vacuum_threshold = 1000 autovacuum_analyze_threshold = 500 autovacuum_vacuum_scale_factor = 0.2 autovacuum_analyze_scale_factor = 0.1 autovacuum_freeze_max_age = 2 #autovacuum_vacuum_cost_delay = 200 #autovacuum_vacuum_cost_limit = -1 -- Please provide you suggestion regarding the same. Many thanks _ The latest in fashion and style in MSN Lifestyle http://lifestyle.in.msn.com/
Re: [PERFORM] Autovaccum settings while Bulk Loading data
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 EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] query hangs
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.activityid =
vtiger_emaildetails.emailid
QUERY
PLAN
-
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: (outer.emailid = inner.activityid)
- Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: (outer.emailid = inner.mailid)
- Index Scan using vtiger_emaildetails_pkey on
vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
- Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
- Seq Scan on vtiger_vantage_email_track
(cost=0.00..1369.52 rows=88852 width=12)
- Index Scan using seactivityrel_activityid_idx on
vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in
('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
+-+--
vtiger_emaildetails| 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
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 to 8.4.4? On Wed, 2010-06-09 at 21:51 -0400, Robert Haas wrote: On Wed, Jun 9, 2010 at 6:56 AM, Max Williams max.willi...@mflow.com 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 limited. Maybe different compile options? If we'd really slowed things down by 50% between 8.4.3 and 8.4.4, there'd be an awful lot of people screaming about it... Given that there are 2 recent reports on the same issue, I wonder if the new packages were built with debugging options or not. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query hangs
2010/6/10 AI Rumman rumman...@gmail.com
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.activityid =
vtiger_emaildetails.emailid
QUERY
PLAN
-
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: (outer.emailid = inner.activityid)
- Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: (outer.emailid = inner.mailid)
- Index Scan using vtiger_emaildetails_pkey on
vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
- Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
- Seq Scan on vtiger_vantage_email_track
(cost=0.00..1369.52 rows=88852 width=12)
- Index Scan using seactivityrel_activityid_idx on
vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in
('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
+-+--
vtiger_emaildetails| 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)
Could you define what you mean by 'hangs'? Does it work or not?
Check table pg_locks for locking issues, maybe the query is just slow but
not hangs.
Notice that the query just returns 2M rows, that can be quite huge number
due to your database structure, data amount and current server
configuration.
regards
Szymon Guz
Re: [PERFORM] query hangs
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 mabew...@gmail.com wrote:
2010/6/10 AI Rumman rumman...@gmail.com
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.activityid =
vtiger_emaildetails.emailid
QUERY
PLAN
-
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: (outer.emailid = inner.activityid)
- Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: (outer.emailid = inner.mailid)
- Index Scan using vtiger_emaildetails_pkey on
vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
- Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
- Seq Scan on vtiger_vantage_email_track
(cost=0.00..1369.52 rows=88852 width=12)
- Index Scan using seactivityrel_activityid_idx on
vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in
('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
+-+--
vtiger_emaildetails| 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)
Could you define what you mean by 'hangs'? Does it work or not?
Check table pg_locks for locking issues, maybe the query is just slow but
not hangs.
Notice that the query just returns 2M rows, that can be quite huge number
due to your database structure, data amount and current server
configuration.
regards
Szymon Guz
Re: [PERFORM] query hangs
2010/6/10 AI Rumman rumman...@gmail.com
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 mabew...@gmail.com wrote:
2010/6/10 AI Rumman rumman...@gmail.com
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.activityid =
vtiger_emaildetails.emailid
QUERY
PLAN
-
Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
Merge Cond: (outer.emailid = inner.activityid)
- Merge Join (cost=9500.30..11658.97 rows=88852 width=498)
Merge Cond: (outer.emailid = inner.mailid)
- Index Scan using vtiger_emaildetails_pkey on
vtiger_emaildetails (cost=0.00..714.40 rows=44595 width=486)
- Sort (cost=9500.30..9722.43 rows=88852 width=12)
Sort Key: vtiger_vantage_email_track.mailid
- Seq Scan on vtiger_vantage_email_track
(cost=0.00..1369.52 rows=88852 width=12)
- Index Scan using seactivityrel_activityid_idx on
vtiger_seactivityrel (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)
select relname, reltuples, relpages
from pg_class
where relname in
('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');
relname | reltuples | relpages
+-+--
vtiger_emaildetails| 44595 | 1360
vtiger_seactivityrel | 1.31978e+06 | 6470
vtiger_vantage_email_track | 88852 | 481
(3 rows)
Could you define what you mean by 'hangs'? Does it work or not?
Check table pg_locks for locking issues, maybe the query is just slow but
not hangs.
Notice that the query just returns 2M rows, that can be quite huge number
due to your database structure, data amount and current server
configuration.
regards
Szymon Guz
1. Make vacuum analyze on used tables.
2. Check how long it would take if you limit the number of returned rows
just to 100
3. Do you have indexes on used columns?
regards
Szymon Guz
Re: [PERFORM] query hangs
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 until all rows have been received and cached on the client side. That's estimated to be over 2.6 million rows in this case. That can take a while. You might want to use a cursor -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query hangs
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) 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 until all rows have been received and cached on the client side. That's estimated to be over 2.6 million rows in this case. That can take a while. You might want to use a cursor -Kevin
Re: [PERFORM] query hangs
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 rumman...@gmail.com 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) 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 until all rows have been received and cached on the client side. That's estimated to be over 2.6 million rows in this case. That can take a while. You might want to use a cursor If you are using psql client, using FETCH_COUNT to a small value will allow you to achieve cursor behaviour. psql starts returning batches of FETCH_COUNT number of rows . E.g. \set FETCH_COUNT 1 will start fetching and displaying each row one by one. -Kevin
Re: [PERFORM] slow query performance
On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu fotogra...@gmail.com 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 on a similar sized table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 ) Well that could certainly matter... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams max.willi...@mflow.com 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 subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] requested shared memory size overflows size_t
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 robertmh...@gmail.com wrote: From: Robert Haas robertmh...@gmail.com Subject: Re: [PERFORM] requested shared memory size overflows size_t To: Bob Lunney bob_lun...@yahoo.com Cc: pgsql-performance@postgresql.org, Tom Wilcox hungry...@googlemail.com Date: Wednesday, June 9, 2010, 9:49 PM On Wed, Jun 2, 2010 at 9:26 PM, Bob Lunney bob_lun...@yahoo.com wrote: Your other option, of course, is a nice 64-bit linux variant, which won't have this problem at all. Although, even there, I think I've heard that after 10GB you don't get much benefit from raising it further. Not sure if that's accurate or not... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
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 Williams max.willi...@mflow.com 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 subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams max.willi...@mflow.com 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.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams max.willi...@mflow.com 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 subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovaccum settings while Bulk Loading data
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 hanging the entire process. What do you mean by hanging the entire process? Hanging the entire process means...the autovacuum and auto analyzes starts and after that there is no acitivity i could see in the postgres log related to the bulk loading and when checked the postgres processes from the task manager i could see few of the postgres porcess are still running and had to be killed from there..normal shut down in not happening in this case... You'll have to provide a lot more details if you want people to help you. How do you bulk load the data? What kind of log messages do you normally get in the PostgreSQL log related to bulk loading? Autovacuum or autoanalyze should not interfere with loading data, even if it runs simultaneously. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query performance
Attached Thank you On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu fotogra...@gmail.com 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 on a similar sized table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 ) Well that could certainly matter... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company add_missing_from| off| Automatically adds missing table references to FROM clauses. allow_system_table_mods | off| Allows modifications of the structure of system tables. archive_command | (disabled) | Sets the shell command that will be called to archive a WAL file. archive_mode| off| Allows archiving of WAL files using archive_command. archive_timeout | 0 | Forces a switch to the next xlog file if a new file has not been started within N seconds. array_nulls | on | Enable input of NULL elements in arrays. authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication. autovacuum | on | Starts the autovacuum subprocess. autovacuum_analyze_scale_factor | 0.1| Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples. autovacuum_analyze_threshold| 50 | Minimum number of tuple inserts, updates or deletes prior to analyze. autovacuum_freeze_max_age | 2 | Age at which to autovacuum a table to prevent transaction ID wraparound. autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes. autovacuum_naptime | 7d | Time to sleep between autovacuum runs. autovacuum_vacuum_cost_delay| 50ms | Vacuum cost delay in milliseconds, for autovacuum. autovacuum_vacuum_cost_limit| -1 | Vacuum cost amount available before napping, for autovacuum. autovacuum_vacuum_scale_factor | 0.2| Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. autovacuum_vacuum_threshold | 1000 | Minimum number of tuple updates or deletes prior to vacuum. backslash_quote | safe_encoding | Sets whether \' is allowed in string literals. bgwriter_delay | 200ms | Background writer sleep time between rounds. bgwriter_lru_maxpages | 100| Background writer maximum number of LRU pages to flush per round. bgwriter_lru_multiplier | 2 | Multiple of the average buffer usage to free per round. block_size | 8192 | Shows the size of a disk block. bonjour_name|| Sets the Bonjour broadcast service name. check_function_bodies | on | Check function bodies during CREATE FUNCTION. checkpoint_completion_target| 0.5| Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval. checkpoint_segments | 128| Sets the maximum distance in log segments between automatic WAL checkpoints. checkpoint_timeout | 5min | Sets the maximum time between automatic WAL checkpoints. checkpoint_warning | 30s| Enables warnings if checkpoint segments are filled more frequently than this. client_encoding | UTF8 | Sets the client's character set encoding. client_min_messages | notice | Sets the message levels that are sent to the client. commit_delay| 0 | Sets the delay in microseconds between transaction commit and flushing WAL to disk. commit_siblings | 5 | Sets the minimum concurrent open transactions before performing commit_delay. constraint_exclusion| partition | Enables the planner to use constraints to optimize queries. cpu_index_tuple_cost| 0.005 | Sets the planner's estimate of the cost of processing each index entry during an index scan. cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call. cpu_tuple_cost | 0.01 | Sets the planner's estimate of the cost of processing each tuple (row). cursor_tuple_fraction | 0.1
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
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...@wicourts.gov] Sent: 10 June 2010 16:16 To: Max Williams; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4? Max Williams max.willi...@mflow.com 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.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams max.willi...@mflow.com 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? Perhaps I should tell the person who builds the pgdg packages?? Yes, the folks at commandprompt need to be told about this. Loudly. It's a serious packaging error. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
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 inconvenience. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query performance
On Thu, Jun 10, 2010 at 11:32 AM, Anj Adu fotogra...@gmail.com 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 timestamp with time zone.
Can we see the index and table definitions of the relevant tables
(attached as a text file) and the size of each one (use select
pg_relation_size('name'))?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance tuning for postgres
On Fri, Jun 4, 2010 at 12:40 AM, Yogesh Naik yogesh_n...@persistent.co.in 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: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query performance
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 robertmh...@gmail.com wrote:
On Thu, Jun 10, 2010 at 11:32 AM, Anj Adu fotogra...@gmail.com 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 timestamp with time zone.
Can we see the index and table definitions of the relevant tables
(attached as a text file) and the size of each one (use select
pg_relation_size('name'))?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Need to increase performance of a query
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(# ) psrdb-# ORDER BY psrdb-# maxRank DESC; QUERY PLAN -- Sort (cost=0.19..0.19 rows=1 width=0) (actual time=12.154..12.155 rows=1 loops=1) Sort Key: ($0) Sort Method: quicksort Memory: 17kB InitPlan - Limit (cost=0.00..0.17 rows=1 width=8) (actual time=12.129..12.130 rows=1 loops=1) - Index Scan Backward using item_rank_rank on item_rank (cost=0.00..2933.84 rows=17558 width=8) (actual time=12.126..12.126 rows=1 loops=1) Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND ((project_id)::text = 'proj2783'::text)) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=12.140..12.142 rows=1 loops=1) Total runtime: 12.206 ms (9 rows) I have been playing with indexes but it seems that it doesn't make any difference. (I have created an index: item_rank_index btree (project_id) WHERE (pf_id IS NULL)) Any advice on how to make it run faster? Thanks a lot, Anne -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 so if you run it a lot you might benefit from a prepared statement. -- Jesper -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 portion is planning cost so if you run it a lot you might benefit from a prepared statement. Hi Jesper, Thanks your response: psrdb=# \timing Timing is on. 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(# ) psrdb-# ORDER BY psrdb-# maxRank DESC; maxrank - 202 (1 row) Time: 12.947 ms It really seems to me that it should take less time. Specially when I see the result with a different where clause like this one: psrdb=# SELECT psrdb-#MAX(item_rank.rank) AS maxRank psrdb-# FROM psrdb-#item_rank item_rank psrdb-# WHERE psrdb-#item_rank.pf_id='plan1408' psrdb-# ORDER BY psrdb-# maxRank DESC; maxrank - 2050400 (1 row) Time: 2.582 ms Thanks, Anne -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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(# psrdb(# ) psrdb-# ORDER BY psrdb-# maxRank DESC; Don't think it does really matter, but why do you sort a resultset consisting of only one row? Sorry, I should have removed the ORDER by (the full query has a union). So without the ORDER by, here are the results: psrdb=# SELECT psrdb-#MAX(item_rank.rank) AS maxRank psrdb-# FROM psrdb-#item_rank item_rank psrdb-# WHERE psrdb-#item_rank.pf_id='plan1408'; maxrank - 2050400 (1 row) Time: 1.516 ms 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; maxrank - 202 (1 row) Time: 13.177 ms Is there anything that can be done for the second one? Thanks, Anne -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 for the fastest query: psrdb=# explain analyze ELECT psrdb-#MAX(item_rank.rank) AS maxRank psrdb-# FROM psrdb-#item_rank item_rank psrdb-# WHERE psrdb-#item_rank.pf_id='plan1408'; ERROR: syntax error at or near ELECT at character 17 psrdb=# explain analyze SELECT psrdb-#MAX(item_rank.rank) AS maxRank psrdb-# FROM psrdb-#item_rank item_rank psrdb-# WHERE psrdb-#item_rank.pf_id='plan1408'; QUERY PLAN Aggregate (cost=8.28..8.29 rows=1 width=8) (actual time=0.708..0.709 rows=1 loops=1) - Index Scan using item_rank_pf on item_rank (cost=0.00..8.27 rows=1 width=8) (actual time=0.052..0.407 rows=303 loops=1) Index Cond: ((pf_id)::text = 'plan1408'::text) Total runtime: 0.761 ms (4 rows) Time: 2.140 ms -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 NULL. I believe the NULLs were stored in the index in earlier releases too, they just couldn't be searched for. I am using postgres 8.3.6. So why doesn't it use my index? Thanks, Anne -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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(#item_rank.project_id='proj2783' psrdb(# AND item_rank.pf_id IS NULL psrdb(# psrdb(# ) psrdb-# ORDER BY psrdb-# maxRank DESC; Don't think it does really matter, but why do you sort a resultset consisting of only one row? Sorry, I should have removed the ORDER by (the full query has a union). So without the ORDER by, here are the results: psrdb=# SELECT psrdb-#MAX(item_rank.rank) AS maxRank psrdb-# FROM psrdb-#item_rank item_rank psrdb-# WHERE psrdb-#item_rank.pf_id='plan1408'; maxrank - 2050400 (1 row) Time: 1.516 ms 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; maxrank - 202 (1 row) Time: 13.177 ms Is there anything that can be done for the second one? Thanks, Anne What about an IS NULL index on pf_id? Regards, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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(# WHERE psrdb(#item_rank.project_id='proj2783' psrdb(# AND item_rank.pf_id IS NULL psrdb(# psrdb(# ) psrdb-# ORDER BY psrdb-# maxRank DESC; Don't think it does really matter, but why do you sort a resultset consisting of only one row? Sorry, I should have removed the ORDER by (the full query has a union). So without the ORDER by, here are the results: psrdb=# SELECT psrdb-#MAX(item_rank.rank) AS maxRank psrdb-# FROM psrdb-#item_rank item_rank psrdb-# WHERE psrdb-#item_rank.pf_id='plan1408'; maxrank - 2050400 (1 row) Time: 1.516 ms 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; maxrank - 202 (1 row) Time: 13.177 ms Is there anything that can be done for the second one? Thanks, Anne What about an IS NULL index on pf_id? Regards, Ken Hi Ken, I have the following index: item_rank_index2 btree (project_id) WHERE (pf_id IS NULL) Are you suggesting something else? Thanks, Anne -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 not used) Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND ((project_id)::text = 'proj2783'::text)) Total runtime: 11.988 ms (6 rows) Time: 13.654 ms try: create index item_rank_null_idx on item_rank(pf_id) where rank IS NOT NULL AND pf_id IS NULL; Joe signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
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 packaged to make the issue completely go away. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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(# ) psrdb-# ORDER BY psrdb-# maxRank DESC; Don't think it does really matter, but why do you sort a resultset consisting of only one row? -- Jochen Erwied | home: joc...@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: j...@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erw...@vodafone.de +49-173-5404164 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 Sauerbruchstr. 17 | work: j...@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erw...@vodafone.de +49-173-5404164 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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_rank.pf_id IS NULL psrdb(# psrdb(# ) psrdb-# ORDER BY psrdb-# maxRank DESC; Don't think it does really matter, but why do you sort a resultset consisting of only one row? Sorry, I should have removed the ORDER by (the full query has a union). So without the ORDER by, here are the results: psrdb=# SELECT psrdb-# MAX(item_rank.rank) AS maxRank psrdb-# FROM psrdb-# item_rank item_rank psrdb-# WHERE psrdb-# item_rank.pf_id='plan1408'; maxrank - 2050400 (1 row) Time: 1.516 ms 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; maxrank - 202 (1 row) Time: 13.177 ms Is there anything that can be done for the second one? 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. You need to create an index that includes the IS NULL condition. 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 not used) 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; QUERY PLAN -- Result (cost=0.17..0.18 rows=1 width=0) (actual time=11.942..11.943 rows=1 loops=1) InitPlan - Limit (cost=0.00..0.17 rows=1 width=8) (actual time=11.931..11.932 rows=1 loops=1) - Index Scan Backward using item_rank_rank on item_rank (cost=0.00..2933.84 rows=17558 width=8) (actual time=11.926..11.926 rows=1 loops=1) Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND ((project_id)::text = 'proj2783'::text)) Total runtime: 11.988 ms (6 rows) Time: 13.654 ms Thanks, Anne -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Need to increase performance of a query
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 NULL; Joe I tried that and it didn't make any difference. Same query plan. Anne -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Analysis Function
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 seconds, but without is 1.5 seconds. The PL/pgSQL code that actually runs (where p_month1, p_day1, and p_month2, p_day2 are integers): *date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''') d1, date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''') d2 * What is a better way to create those dates (without string concatenation, I presume)? Dave
Re: [PERFORM] slow query performance
On Thu, Jun 10, 2010 at 12:58 PM, Anj Adu fotogra...@gmail.com 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 think it's going to be hard to troubleshoot this without seeing those definitions (and also the sizes, which I asked for in my previous email). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 and upwards can use an index for IS NULL. I believe the NULLs were stored in the index in earlier releases too, they just couldn't be searched for. I am using postgres 8.3.6. So why doesn't it use my index? Well, apparently the planner doesn't think it would be any cheaper. I wonder if this helps: CREATE INDEX item_rank_project_id ON item_rank(project_id, rank, pf_id); And make sure you drop any of the indexes that are not being used, to make sure the planner doesn't choose them instead. (You should upgrade to 8.3.11, BTW. There's been a bunch of bug-fixes in-between, though I don't know if any are related to this, but there's other important fixes there) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need to increase performance of a query
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 digital signature
Re: [PERFORM] Need to increase performance of a query
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 it does. Thanks a lot! Anne -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Analysis Function
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 c.id http://c.id = 5148 AND ... Date extraction is 3.2 seconds, but without is 1.5 seconds. The PL/pgSQL code that actually runs (where p_month1, p_day1, and p_month2, p_day2 are integers): *date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''') d1, date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''') d2 * What is a better way to create those dates (without string concatenation, I presume)? Dave I assume you are doing this in a loop? Many Many Many times? cuz: andy=# select date(extract(year from current_date) || '-1-1'); date 2010-01-01 (1 row) Time: 0.528 ms Its pretty quick. You say without its 1.5 seconds? Thats all you change? Can we see the sql and 'explain analyze' for both? -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query performance
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 fotogra...@gmail.com wrote: Attached Thank you On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu fotogra...@gmail.com 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 on a similar sized table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 ) Well that could certainly matter... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Analysis Function
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.depesz.com/s/DW2
Time: 2.6s
Variation #2
date('1960'||'-1-1')
Explain: http://explain.depesz.com/s/YuX
Time: 3.1s
Variation #3
date(extract(YEAR FROM m.taken)||'-1-1')
Explain: http://explain.depesz.com/s/1I
Time: 4.3s
Variation #4
to_date( date_part('YEAR', m.taken)::text, '' ) + interval '0 months' +
interval '0 days'
Explain: http://explain.depesz.com/s/fIT
Time: 4.4s
What I would like is along Variation #5:
*PGTYPESdate_mdyjul(taken_year, p_month1, p_day1)*
Time: 2.3s
I find it interesting that variation #2 is half a second slower than
variation #1.
The other question I have is: why does PG seem to discard the results? In
pgAdmin3, I can keep pressing F5 and (before 8.4.4?) the results came back
in 4s for the first response then 1s in subsequent responses.
Dave
Re: [PERFORM] query hangs
On 10 June 2010 18:47, AI Rumman rumman...@gmail.com 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 rumman...@gmail.com 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) 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 until all rows have been received and cached on the client side. That's estimated to be over 2.6 million rows in this case. That can take a while. You might want to use a cursor If you are using psql client, using FETCH_COUNT to a small value will allow you to achieve cursor behaviour. psql starts returning batches of FETCH_COUNT number of rows . E.g. \set FETCH_COUNT 1 will start fetching and displaying each row one by one. -Kevin
