[GENERAL] Very long execution time of select nextval('..');
Hi,
we run postgresql-8.1 on a dedicated debian box 64bit, dual-core CPU, 8GB RAM,
RAID-1.
We select our primary keys with select nextval before we actually insert a
record. In my logs i print every statement which takes longer than 250ms
there are lots of values fetched each day with nextval, but i have about 20-50
statements each day which take up to 17 seconds(!):
LOG: duration: 12636.746 ms statement: EXECUTE unnamed [PREPARE: select
nextval ('member_id_seq')]
[it is always the same statement so i just show some numbers:
LOG: duration: 4000.991 ms ...
LOG: duration: 2994.532 ms ...
LOG: duration: 611.167 ms ...
LOG: duration: 17072.196 ms
LOG: duration: 16570.860 ms
LOG: duration: 14816.153 ms
LOG: duration: 265.855 ms
LOG: duration: 1238.361 ms
...
there are about 50 commands per day like this taking longer than 250ms.
i have no idea why something like nextval() can take longer than 1ms.
and if i do it manually it shows up like this:
select nextval ('member_id_seq');
nextval
-
569304
(1 row)
Time: 0.651 ms
Unfortunatly i can not tell at which time this happens as the log doesn't
show the time of day.
As it is a web application everything above 250ms is not acceptable. Something
like 17seconds (!) is like not working at all.
i observe my server with munin and the load of the server is at the maximum
about 0.5.
Can this be related to the autovacuum process, which we run every 50 minutes?
It seems that something is blocked. But i thought that nextval is never
blocked by anyhing else.
now i looked at some stats
select blks_read, blks_hit from pg_statio_user_sequences;
( i dropped names and relids as they are not important.)
blks_read | blks_hit
---+--
2 |0
22 | 125
14 | 142
2 |0
14 |0
43 | 498
27 | 24
34 |0
25 | 12
55 | 55
8 | 17
2 |0
14 |0
34 |0
2 |0
33 | 539
58 | 25
59 | 53
34 |0
2 |0
2 |0
39 | 135
2 |0
38 |4
34 |0
2 |0
14 |0
15 | 131
23 | 1223
22 | 147
1 | 2015
10 |2
13 | 67
34 |0
34 |0
32 | 3610
46 | 590
I dont have any clue what is happening but something runs rather suboptimal.
Any help is very appreciated.
kind regards,
Janning
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Very long execution time of select nextval('..');
[EMAIL PROTECTED] wrote: Unfortunatly i can not tell at which time this happens as the log doesn't show the time of day. Start with your postgresql.conf - http://www.postgresql.org/docs/8.2/interactive/runtime-config-logging.html points of interest :- log_min_messages - debug1 to get some more info in the log log_line_prefix - %t will show the timestamp of the log entry log_statement - can record the queries to the log so you can see just what is being run as you go through the log. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)
I think we finally found the problem. Please see if things are more stable with 8.3RC1 plus this patch: http://archives.postgresql.org/pgsql-committers/2008-01/msg00190.php Sorry for being so late. I'm currently using PostgreSQL RC2 at work and I notice no crashes. Thanks! -- Lawrence, stacktrace.it - oluyede.org - neropercaso.it It is difficult to get a man to understand something when his salary depends on not understanding it - Upton Sinclair ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] A select DISTINCT query?
Hello try SELECT DISTINCT col FROM table Pavel On 27/01/2008, Phil Rhoades [EMAIL PROTECTED] wrote: People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] A select DISTINCT query?
People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] A select DISTINCT query?
Phil Rhoades wrote: People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 SELECT count(*) as cnt,a,b,c FORM yourtable GROUP BY a,b,c HAVING cnt=1 should do. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A select DISTINCT query?
Pavel, You didn't read my note properly - your query gives: 1 2 3 4 5 I want: 1 2 4 5 Phil. On Sun, 2008-01-27 at 15:10 +0100, Pavel Stehule wrote: Hello try SELECT DISTINCT col FROM table Pavel On 27/01/2008, Phil Rhoades [EMAIL PROTECTED] wrote: People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] A select DISTINCT query?
Tino, On Sun, 2008-01-27 at 15:16 +0100, Tino Wildenhain wrote: Phil Rhoades wrote: People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 SELECT count(*) as cnt,a,b,c FORM yourtable GROUP BY a,b,c HAVING cnt=1 should do. I get: SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; ERROR: column cnt does not exist LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; ^ Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] A select DISTINCT query?
On 27/01/2008, Phil Rhoades [EMAIL PROTECTED] wrote: Tino, On Sun, 2008-01-27 at 15:16 +0100, Tino Wildenhain wrote: Phil Rhoades wrote: People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 SELECT count(*) as cnt,a,b,c FORM yourtable GROUP BY a,b,c HAVING cnt=1 should do. I get: SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; ERROR: column cnt does not exist LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; ^ select count(*) as cnt, name from tst group by name having count(*) = 1 Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A select DISTINCT query?
Guys, On Sun, 2008-01-27 at 17:38 +0100, Pavel Stehule wrote: On 27/01/2008, Phil Rhoades [EMAIL PROTECTED] wrote: Tino, On Sun, 2008-01-27 at 15:16 +0100, Tino Wildenhain wrote: Phil Rhoades wrote: People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 SELECT count(*) as cnt,a,b,c FORM yourtable GROUP BY a,b,c HAVING cnt=1 should do. I get: SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; ERROR: column cnt does not exist LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; ^ select count(*) as cnt, name from tst group by name having count(*) = 1 Muchas gracias! Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A select DISTINCT query?
On Mon, Jan 28, 2008 at 03:32:18AM +1100, Phil Rhoades wrote: SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; ERROR: column cnt does not exist LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; having count(*) = 1; depesz -- quicksil1er: postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV! :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Very long execution time of select nextval('..');
[EMAIL PROTECTED] writes:
we run postgresql-8.1 on a dedicated debian box 64bit, dual-core CPU, 8GB
RAM,
RAID-1.
8.1.what?
LOG: duration: 12636.746 ms statement: EXECUTE unnamed [PREPARE: select
nextval ('member_id_seq')]
That's just bizarre, especially if your system isn't showing any other
signs of stress.
Unfortunatly i can not tell at which time this happens as the log doesn't
show the time of day.
See log_line_prefix. I think what you need to do is gather some
evidence about what else is happening at the same time --- can you
afford to enable log_statement = all? Also, you should try to correlate
this with spikes in I/O demand (try running vmstat 1 or similar).
It could be that this is related to checkpointing, which you won't see
in a log_statement trace. In 8.1 you'd have to crank up
log_min_messages to DEBUG2 to get log entries for checkpoint start and
end, which is going to result in a mighty verbose log, but you may have
to do that to confirm or disprove the idea.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A select DISTINCT query? - followup Q
People, select count(*) as cnt, name from tst group by name having count(*) = 1 This worked for my basic example but not for my actual problem - I get column comment must appear in the GROUP BY clause or be used in an aggregate function errors so I have a related question: With table: name comment 1first comment 2second comment 3third comment 3fourth comment 4fifth comment 5sixth comment - how can I use something like the previous select statement but where the comment field does not appear in the group by clause and gives the following result: 1first comment 2second comment 4fifth comment 5sixth comment Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A select DISTINCT query? - followup Q
Hi Phil, Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. SELECT COUNT(*) AS cnt, name, comment, ... FROM tst GROUP BY name, comment, ... HAVING COUNT(*) = 1; Phil Rhoades wrote: People, select count(*) as cnt, name from tst group by name having count(*) = 1 This worked for my basic example but not for my actual problem - I get column comment must appear in the GROUP BY clause or be used in an aggregate function errors so I have a related question: With table: name comment 1first comment 2second comment 3third comment 3fourth comment 4fifth comment 5sixth comment - how can I use something like the previous select statement but where the comment field does not appear in the group by clause and gives the following result: 1first comment 2second comment 4fifth comment 5sixth comment Thanks, Phil. Mike Ginsburg Collaborative Fusion, Inc. [EMAIL PROTECTED] 412-422-3463 x4015 -- IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] A select DISTINCT query? - followup Q
On Sunday 27 January 2008 10:56:18 am Mike Ginsburg wrote: Hi Phil, Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. SELECT COUNT(*) AS cnt, name, comment, ... FROM tst GROUP BY name, comment, ... HAVING COUNT(*) = 1; Is the requirement of select fields matching group by fields a SQL92 requirement or something to due to Postgres? I ask because with Visual Fox Pro I know that I can have several select fields with only one group by field. -- John Fabiani ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A select DISTINCT query? - followup Q
johnf [EMAIL PROTECTED] writes: On Sunday 27 January 2008 10:56:18 am Mike Ginsburg wrote: Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. Is the requirement of select fields matching group by fields a SQL92 requirement or something to due to Postgres? I ask because with Visual Fox Pro I know that I can have several select fields with only one group by field. It is in fact a SQL92 requirement: section 7.9 query specification saith 7) If T is a grouped table, then each column reference in each value expression that references a column of T shall refer- ence a grouping column or be specified within a set function specification. (A set function is what PG calls an aggregate function.) Later versions of the spec relax that a bit: in SQL99, if you GROUP BY a primary key (or some other cases that are not too interesting in practice) then there can be only one row per group anyway and so references to other columns will have well-defined values. We have not got around to implementing that extension. I don't know FoxPro, but there are some DBMSes (cough m***l cough) that simply let you reference ungrouped columns without any check to see whether what you have written is sensible or not. The results you get from such a query are pretty unpredictable, or at least implementation- dependent. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A select DISTINCT query? - followup Q
Mike, I can't do that with my comments - I get all six of the records in the result with the example instead of just four like I want . . but someone else had a solution without using the group by clause . . Phil. On Sun, 2008-01-27 at 13:56 -0500, Mike Ginsburg wrote: Hi Phil, Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. SELECT COUNT(*) AS cnt, name, comment, ... FROM tst GROUP BY name, comment, ... HAVING COUNT(*) = 1; Phil Rhoades wrote: People, select count(*) as cnt, name from tst group by name having count(*) = 1 This worked for my basic example but not for my actual problem - I get column comment must appear in the GROUP BY clause or be used in an aggregate function errors so I have a related question: With table: name comment 1first comment 2second comment 3third comment 3fourth comment 4fifth comment 5sixth comment - how can I use something like the previous select statement but where the comment field does not appear in the group by clause and gives the following result: 1first comment 2second comment 4fifth comment 5sixth comment Thanks, Phil. Mike Ginsburg Collaborative Fusion, Inc. [EMAIL PROTECTED] 412-422-3463 x4015 -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Very long execution time of select nextval('..');
Am Sonntag 27 Januar 2008 18:56:49 schrieb Tom Lane:
[EMAIL PROTECTED] writes:
we run postgresql-8.1 on a dedicated debian box 64bit, dual-core CPU, 8GB
RAM, RAID-1.
8.1.what?
8.1.11-0etch1
LOG: duration: 12636.746 ms statement: EXECUTE unnamed [PREPARE:
select nextval ('member_id_seq')]
That's just bizarre, especially if your system isn't showing any other
signs of stress.
Unfortunatly i can not tell at which time this happens as the log
doesn't show the time of day.
See log_line_prefix. I think what you need to do is gather some
evidence about what else is happening at the same time --- can you
afford to enable log_statement = all? Also, you should try to correlate
this with spikes in I/O demand (try running vmstat 1 or similar).
i guess log_statement=all will use all IO of my disks as there are lots of
queries
It could be that this is related to checkpointing, which you won't see
in a log_statement trace. In 8.1 you'd have to crank up
log_min_messages to DEBUG2 to get log entries for checkpoint start and
end, which is going to result in a mighty verbose log, but you may have
to do that to confirm or disprove the idea.
ok, at the moment i got some traffic and my load is at 1.5. But now with
logging the timestamp I have seen that the long durations are quite regular
at intervals of 10 minutes.
but what in hell can make nextval take so long? even if checkpointing is badly
configured. I always thought that nextval is one of the fastest operations.
So if it takes 500 ms, fine. things like this can always happen, but 20
seconds sounds more like a hardware failure. But i can't see any.
ok, i will do some research tomorrow as i 'll try to go to sleep now.
kind regards
Janning
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Very long execution time of select nextval('..');
[EMAIL PROTECTED] writes: ok, at the moment i got some traffic and my load is at 1.5. But now with logging the timestamp I have seen that the long durations are quite regular at intervals of 10 minutes. Well, that's pretty suggestive. Tell us about your checkpoint and bgwriter settings. Also, is there any other service running on the machine that might have activity spikes every 10 minutes? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Very long execution time of select nextval('..');
On Sun, 27 Jan 2008, [EMAIL PROTECTED] wrote: ok, at the moment i got some traffic and my load is at 1.5. But now with logging the timestamp I have seen that the long durations are quite regular at intervals of 10 minutes. Sure sounds like checkpoints. You should turn on the checkpoint warning feature so it always triggers and see if the long queries completely just after the checkpoints finish. Notes on that and what you can do to possibly improve checkpoint behavior are at http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm The early parts of that mostly refer to 8.2 but 8.1 is basically the same in this area. but what in hell can make nextval take so long? even if checkpointing is badly configured. You're in a situation where your amount of RAM far exceeds your disk I/O capabilities. Brutally bad checkpoints are easy to encounter in that setup. Linux by default will use 10% of RAM to hold writes. At checkpoint time, that entire Linux buffer cache has to be cleared of database writes on top of what's written by the checkpoint itself. How long do you think it takes to write 800MB of database data with a significant random-access component to it when your disk is a simple RAID-1? 20 seconds is not out of the question. You may want to significantly reduce the size of the Linux write buffer and see if that helps. http://www.westnet.com/~gsmith/content/linux-pdflush.htm goes over theory and suggestions here. I always thought that nextval is one of the fastest operations. So if it takes 500 ms, fine. things like this can always happen, but 20 seconds sounds more like a hardware failure. But i can't see any. Just about everything gets blocked behind the worse checkpoint spikes. The thing that kind of bothers me about your case is that I'd expect other queries would also be blocked and you'd have a whole set of 250ms ones lined up just after the checkpoint is done. That you're only reporting issues with nextval makes me wonder if there isn't some other locking driving the main behavior, perhaps something that just gets worse at checkpoint time rather than being directly caused by it. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] match accented chars with ASCII-normalised version
On Jan 25, 2008 12:02 AM, brian [EMAIL PROTECTED] wrote: The client for a web application I'm working on wants certain URLs to contain the full names of members (SEO-friendly links). Scripts would search on, say, a member directory entry based on the name of the member, rather than the row ID. I can easily join first last names with an underscore (and split on that later) and replace spaces with +, etc. But many of the names contain multibyte characters and so the URLs would become URL-encoded, eg: Adelina España - Adelina_Espa%C3%B1a The client won't like this (and neither will I). I can create a conversion array to replace certain characters with 'normal' ones: Adelina_Espana However, I then run into the problem of trying to match 'Espana' to 'España'. Searching online, I found a few ideas (soundex, intuitive fuzzy something-or-other) but mostly they seem like overkill for this application. what about using to_ascii() ? http://www.postgresql.org/docs/8.3/static/functions-string.html -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [OT] Slony Triggers pulling down performance?
On Fri, 2008-01-18 at 14:57 -0500, Chris Browne wrote: [EMAIL PROTECTED] (Ow Mun Heng) writes: Just wondering if my 'Perceived' feeling that since implementing slony for master/slave replication of select tables, my master database performance is getting slower. I'm constantly seeing a very high amount of IO wait. ~40-80 according to vmstat 1 and according to atop. (hdb/hdc = raid1 mirror) DSK | hdb | busy 83% | read1052 | write 50 | avio7 ms | DSK | hdc | busy 81% | read1248 | write 49 | avio6 ms | The triggers generate some extra I/O, as they go off and write tuples into sl_log_1/sl_log_2, so there's certainly a cost, there. When you pull data from sl_log_1/sl_log_2, that will have a cost, too. Replication does not come at zero cost... I've been battling with this issus for the past week and that prompted a few changes in the manner I pull the data and in the location where i store the data. I ended up implementing partitioning on the 2 main largest (problematic) tables and put it intp weekly rotation and moved the broke the 3 disk raid1(1 spare) spare disk and used that as the slony-I sl_log_1/sl_log_2 tablespace. Now, everything is back to normal. (until I break it again!!) IO Wait is hovering between 0-40% ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] what is it that \d actually does
On Jan 25, 2008 2:41 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Jan 25, 2008 1:34 PM, Geoffrey [EMAIL PROTECTED] wrote: It seems that I recall there is a way to display the actual select statement that is executed when you execute the \d command. psql --help says: -E display queries that internal commands generate also, you can log statements to the server with log_statement. psql -E is probably better though. merlin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Slow Query problem
Dear All,I am currently using PostgreSQL database version 8.0.13. My problem relates to a slow result when a query using a defined view joins to another table for a result.Background: I have 7 tables of invoice transactions. The tables are slightly different in that they record different data (some different columns in each table). There are about 250,000 records when a union view is created. A simply query on this union performs satisfactorily. The invoice table union view is then joined with a table of receipts (which have a total of about 150,000 records). It takes around 3.5 seconds for "select * from view_transaction where member_id = 999 and receipt_no is null" (which returns unpaid invoices). By hard coding I created a single table from the 7 invoice tables (instead of creating a union) and then used it with receipt table. This time for the same query improved to 1.8 seconds. To further improve things I tried to code the selection rather than to use a view, and so "select * from temp_transaction where member_id = 999 and receipt_no is null" provided the result in .5 second. (2 records returned containing the details of receipt_no, transaction_no, transaction_type, transaction_amount, member_id). I would prefer to be able to have completed the above by using unions and views. Is it possible to do this, or am I better creating a permanent table of invoices and writing the query as I did above? Any comments on this and suggestions would be appreciated. If there is documentation where I can read up please let me have a link.Thank You,Premsun NETsolutions Asia Limited +66 (2) 237 7247
