Re: [PERFORM] sunquery and estimated rows
Litao Wu <[EMAIL PROTECTED]> writes: > When I included a subquery, the estimated rows (1240) > is way too high as shown in the following example. > select * from test > where scope=(select 10); The planner sees that as "where scope = " and falls back to a default estimate. It won't simplify a sub-select to a constant. (Some people consider that a feature ;-).) The estimate should still be derived from the statistics for the scope column, but it will just depend on the number of distinct values for the column and not on the specific comparison constant. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Poor performance of group by query
> stats=# explain analyze SELECT work_units, min(raw_rank) AS rank FROM > Trank_work_overall GROUP BY work_units; > > ... > > raw_rank | bigint | > work_units | bigint | If you create a copy of the same table using regular integers does that run fast? And a copy of the table using bigints is still slow like the original? I know bigints are less efficient than integers because they're handled using dynamically allocated memory. This especially bites aggregate functions. But I don't see why it would be any slower for a hash aggregate than a regular aggregate. It's a pretty gross amount of time for 18k records. There was a thought a while back about making 64-bit machines handle 64-bit datatypes like bigints without pointers. That would help on your Opteron. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] query slows down with more accurate stats
On Fri, 16 Apr 2004 10:34:49 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >> p = prod from{i = 0} to{n - 1} {{c(B - i)} over {cB - i}} > >So? You haven't proven that either sampling method fails to do the >same. On the contrary, I believe that above formula is more or less valid for both methods. The point is in what I said next: | This probability grows with increasing B. For the one-stage sampling method B is the number of pages of the whole table. With two-stage sampling we have to use n instead of B and get a smaller probability (for n < B, of course). So this merely shows that the two sampling methods are not equivalent. >The desired property can also be phrased as "every tuple should be >equally likely to be included in the final sample". Only at first sight. You really expect more from random sampling. Otherwise I'd just put one random tuple and its n - 1 successors (modulo N) into the sample. This satisfies your condition but you wouldn't call it a random sample. Random sampling is more like "every possible sample is equally likely to be collected", and two-stage sampling doesn't satisfy this condition. But if in your opinion the difference is not significant, I'll stop complaining against my own idea. Is there anybody else who cares? >You could argue that a tuple on a heavily populated page is >statistically likely to see a higher T when it's part of the page sample >pool than a tuple on a near-empty page is likely to see, and therefore >there is some bias against selection of the former tuple. But given a >sample over a reasonably large number of pages, the contribution of any >one page to T should be fairly small and so this effect ought to be >small. It is even better: Storing a certain number of tuples on heavily populated pages takes less pages than to store them on sparsely populated pages (due to tuple size or to dead tuples). So heavily populated pages are less likely to be selected in stage one, and this exactly offsets the effect of increasing T. >So I think this method is effectively unbiased at the tuple level. Servus Manfred ---(end of broadcast)--- TIP 3: 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
[PERFORM] sunquery and estimated rows
Hi, When I included a subquery, the estimated rows (1240) is way too high as shown in the following example. Can someone explain why? Because of this behavior, some of our queries use hash join instead of nested loop. Thanks, select version(); version - PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) \d test Table "public.test" Column | Type | Modifiers -+--+--- id | integer | name| character varying(255) | d_id| integer | c_id| integer | r_id| integer | u_id| integer | scope | integer | active | integer | created | timestamp with time zone | typ | integer | Indexes: test_scope_idx btree (scope) reindex table test; vacuum full analyze test; select count(*) from test; count --- 4959 (1 row) select count(*) from test where scope=10; count --- 10 (1 row) explain analyze select * from test where scope=10; -- so far so good, estimate 12 rows, actual 10 rows QUERY PLAN -- Index Scan using test_scope_idx on test (cost=0.00..4.35 rows=12 width=59) (actual time=0.04..0.11 rows=10 loops=1) Index Cond: (scope = 10) Total runtime: 0.23 msec (3 rows) explain analyze select * from test where scope=(select 10); -- estimate rows is way too high, do not why QUERY PLAN - Index Scan using test_scope_idx on test (cost=0.00..40.74 rows=1240 width=59) (actual time=0.06..0.13 rows=10 loops=1) Index Cond: (scope = $0) InitPlan -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) Total runtime: 0.22 msec (5 rows) __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Index Problem?
Tom Lane wrote: Josh Berkus <[EMAIL PROTECTED]> writes: A better way to set this would be to run VACUUM VERBOSE ANALYZE right after doing one of your update batches, and see how many dead pages are being reclaimed, and then set max_fsm_pages to that # + 50% (or more). Actually, since he's running 7.4, there's an even better way. Do a "VACUUM VERBOSE" (full-database vacuum --- doesn't matter whether you ANALYZE or not). At the end of the very voluminous output, you'll see something like INFO: free space map: 240 relations, 490 pages stored; 4080 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. Here, I would need max_fsm_relations = 240 and max_fsm_pages = 4080 to exactly cover the present freespace needs of my system. I concur with the suggestion to bump that up a good deal, of course, but that gives you a real number to start from. The DETAIL part of the message shows my current settings (which are the defaults) and what the FSM is costing me in shared memory space. Okay, after running the function VACUUM VERBOSE is telling me: INFO: free space map: 136 relations, 25014 pages stored; 22608 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. My max_fsm_pages was set to 20,000 and I reset it to 40,000 on the dev server and the function ran about 20-30% faster, so I'll try the same on the production server. Thanks for the analysis of the VACUUM info. Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Long running queries degrade performance
On Friday 16 April 2004 5:12 pm, Tom Lane wrote: > Chris Kratz <[EMAIL PROTECTED]> writes: > > ... Or if worse comes to worse to actually kill long running > > processes without taking down the whole db as we have had to do on > > occasion. > > A quick "kill -INT" suffices to issue a query cancel, which I think is > what you want here. You could also consider putting an upper limit on > how long things can run by means of statement_timeout. Wow, that's exactly what I've been looking for. I thought I had scoured the manuals, but must have missed that one. I need to think about the statement_timeout, the might be a good idea to use as well. > Those are just band-aids though. Not sure about the underlying problem. > Ordinarily I'd guess that the big-hog queries are causing trouble by > evicting everything the other queries need from cache. But since your > database fits in RAM, that doesn't seem to hold water. That makes some sense, perhaps there is some other cache somewhere that is causing the problems. I am doing some tuning and have set the following items in our postgresql.conf: shared_buffers = 4096 max_fsm_relations = 1000 max_fsm_pages = 2 sort_mem = 2048 effective_cache_size = 64000 I believe these are the only performance related items we've modified. One thing I did today, since we seem to run about 600M of memory available for file caches. The effective cache size used to be much lower, so perhaps that was causing some of the problems. > What PG version are you running? 7.3.4 with grand hopes to move to 7.4 this summer. > regards, tom lane -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Long running queries degrade performance
Chris Kratz <[EMAIL PROTECTED]> writes: > ... Or if worse comes to worse to actually kill long running > processes without taking down the whole db as we have had to do on occasion. A quick "kill -INT" suffices to issue a query cancel, which I think is what you want here. You could also consider putting an upper limit on how long things can run by means of statement_timeout. Those are just band-aids though. Not sure about the underlying problem. Ordinarily I'd guess that the big-hog queries are causing trouble by evicting everything the other queries need from cache. But since your database fits in RAM, that doesn't seem to hold water. What PG version are you running? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Long running queries degrade performance
On Friday 16 April 2004 4:25 pm, Mike Nolan wrote: > Given the intermittent nature of the problem and its relative brevity > (5-10 seconds), I don't know whether top offers the granularity needed to > locate the bottleneck. Our long running processes run on the order of multiple minutes (sometimes for over an hour) and it's expected because the sql can be quite complex over somewhat large datasets. But it's the bringing the server to it's knees, that I'm trying to figure out how to address if we can. In other words, let those long running processes run, but somehow still get decent performance for "quick" requests. Yours reminds me of what used to happen in our apps back when I worked in java and the garbage collector kicked in. Suddenly everything would stop for 10-15s and then continue on. Sort of makes you think the app froze for some reason. > It happens on my development system, and I'm the only one on it. I know > I've seen it on the production server, but I think it is a bit more > common on the development server, though that may be a case of which system > I spend the most time on. (Also, the production server is 1300 miles away > with a DSL connection, so I may just be seeing network delays some of > the time there.) Interesting. Have you tried running a processor monitor and seeing if you are getting a cpu or disk spike when you get the blips? Postgres has been pretty constant for us in it's average runtime for any particular query. We do get some fluctuation, but I've always attributed that to other things happening in the background. I sometimes run gkrellm off the server just to "see" what's happening on a macro scale. It's a great early indicator when we are getting slammed one way or another (network, memory, processor, disk, etc). Plus it shows a couple of seconds of history so you can see blips pretty easily. > My web app traps double-clicks in javascript and ignores all but the first > one. That's because some of the users have mice that give double-clicks > even when they only want one click. Hmmm, never thought of doing that. Might be interesting to do something like that in a few key places where we have problems. > -- > Mike Nolan -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Long running queries degrade performance
> Fairly sure, when it is happening, postgres usually is taking up the top slots > for cpu usage as reported by top. Perhaps there is a better way to monitor > this? Given the intermittent nature of the problem and its relative brevity (5-10 seconds), I don't know whether top offers the granularity needed to locate the bottleneck. > likely you have a situation where something else is happening which blocks > the current thread. It happens on my development system, and I'm the only one on it. I know I've seen it on the production server, but I think it is a bit more common on the development server, though that may be a case of which system I spend the most time on. (Also, the production server is 1300 miles away with a DSL connection, so I may just be seeing network delays some of the time there.) > Both of these were triggered by users double clicking links in our > web app and were fixed by a better implementation. Perhaps something like > that is causing what you are seeing. My web app traps double-clicks in javascript and ignores all but the first one. That's because some of the users have mice that give double-clicks even when they only want one click. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index Problem?
Josh Berkus <[EMAIL PROTECTED]> writes: > A better way to set this would be to run VACUUM VERBOSE ANALYZE right after > doing one of your update batches, and see how many dead pages are being > reclaimed, and then set max_fsm_pages to that # + 50% (or more). Actually, since he's running 7.4, there's an even better way. Do a "VACUUM VERBOSE" (full-database vacuum --- doesn't matter whether you ANALYZE or not). At the end of the very voluminous output, you'll see something like INFO: free space map: 240 relations, 490 pages stored; 4080 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. Here, I would need max_fsm_relations = 240 and max_fsm_pages = 4080 to exactly cover the present freespace needs of my system. I concur with the suggestion to bump that up a good deal, of course, but that gives you a real number to start from. The DETAIL part of the message shows my current settings (which are the defaults) and what the FSM is costing me in shared memory space. If you have multiple active databases, the best approach to getting these numbers is to VACUUM in each one, adding VERBOSE when you do the last one. The FSM report is cluster-wide but you want to be sure the underlying info is up to date for all databases. >> Also the docs on Varlena show the max_fsm_pages >> default to be 10,000 but my default was 20,000, looks like that needs >> updating. > I don't think the default has been changed. Anyone? Yes, I kicked it up for 7.4 because FSM covers indexes too now. Both the 7.3 and 7.4 defaults are pretty arbitrary of course... regards, tom lane ---(end of broadcast)--- TIP 3: 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: [PERFORM] Index Problem?
Ron, > Yeah I agree but I'm not allowed to remove those indexes. It's not the indexes I'm talking about, it's the table. > On my dev server I increased max_fsm_pages from the default of 2 to > 4, A better way to set this would be to run VACUUM VERBOSE ANALYZE right after doing one of your update batches, and see how many dead pages are being reclaimed, and then set max_fsm_pages to that # + 50% (or more). increased checkpoint_segments from 3 to 5, and the function ran > in about 6-7 minutes which is a nice increase. According to the docs > "Annotated postgresql.conf and Global User Configuration (GUC) Guide" on > varlena I'll have to re-start postgres for the changes to take effect > there (correct?). Correct. > Also the docs on Varlena show the max_fsm_pages > default to be 10,000 but my default was 20,000, looks like that needs > updating. I don't think the default has been changed. Anyone? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Deleting certain duplicates
Thanks Mallah, I will keep this example in case I need it again sometime in the future. Unfortunately, I do not have enough free space at the moment to create a temp table. Dan -Original Message- From: Rajesh Kumar Mallah [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 13, 2004 10:27 AM To: Shea,Dan [CIS] Cc: Postgres Performance Subject: Re: [PERFORM] Deleting certain duplicates Shea,Dan [CIS] wrote: >The index is >Indexes: >"forecastelement_rwv_idx" btree (region_id, wx_element, valid_time) > >-Original Message- >From: Shea,Dan [CIS] [mailto:[EMAIL PROTECTED] >Sent: Monday, April 12, 2004 10:39 AM >To: Postgres Performance >Subject: [PERFORM] Deleting certain duplicates > > >We have a large database which recently increased dramatically due to a >change in our insert program allowing all entries. >PWFPM_DEV=# select relname,relfilenode,reltuples from pg_class where relname >= 'forecastelement'; > relname | relfilenode | reltuples >-+-+- > forecastelement | 361747866 | 4.70567e+08 > > Column |Type | Modifiers >+-+--- > version| character varying(99) | > origin | character varying(10) | > timezone | character varying(99) | > region_id | character varying(20) | > wx_element | character varying(99) | > value | character varying(99) | > flag | character(3)| > units | character varying(99) | > valid_time | timestamp without time zone | > issue_time | timestamp without time zone | > next_forecast | timestamp without time zone | > reception_time | timestamp without time zone | > >The program is supposed to check to ensure that all fields but the >reception_time are unique using a select statement, and if so, insert it. >Due an error in a change, reception time was included in the select to check >for duplicates. The reception_time is created by a program creating the dat >file to insert. >Essentially letting all duplicate files to be inserted. > >I tried the delete query below. >PWFPM_DEV=# delete from forecastelement where oid not in (select min(oid) >from forecastelement group by >version,origin,timezone,region_id,wx_element,value,flag,units,valid_time,is s >ue_time,next_forecast); >It ran for 3 days creating what I assume is an index in pgsql_tmp of the >group by statement. >The query ended up failing with "dateERROR:write failed". >Well the long weekend is over and we do not have the luxury of trying this >again. >So I was thinking maybe of doing the deletion in chunks, perhaps based on >reception time. > > its more of an sql question though. to deduplicate on basis of version,origin,timezone,region_id,wx_element,value,flag,units,valid_time, issue_time,next_forecast You could do this. begin work; create temp_table as select distinct on (version,origin,timezone,region_id,wx_element,value,flag,units,valid_time, issue_time,next_forecast) * from forecastelement ; truncate table forecastelement ; drop index ; insert into forecastelement select * from temp_table ; commit; create indexes Analyze forecastelement ; note that distinct on will keep only one row out of all rows having distinct values of the specified columns. kindly go thru the distinct on manual before trying the queries. regds mallah. >Are there any suggestions for a better way to do this, or using multiple >queries to delete selectively a week at a time based on the reception_time. >I would say there are a lot of duplicate entries between mid march to the >first week of April. > > > >---(end of broadcast)--- >TIP 8: explain analyze is your friend > >---(end of broadcast)--- >TIP 3: 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Long running queries degrade performance
Fairly sure, when it is happening, postgres usually is taking up the top slots for cpu usage as reported by top. Perhaps there is a better way to monitor this? The other thing for us is that others talk about disks being the bottleneck whereas for us it is almost always the processor. I expected the drives to kill us early on (we have two uw scsi mirrored drives) but there is very little disk activity. The disks rarely do much during load for us (at this point). Most likely this is related more to data volume at this point. As far as in your case, is there a lot of disk activity happening? More likely you have a situation where something else is happening which blocks the current thread. We ran into two situations recently which exhibited this behavior. One was adding and dropping tables in a transaction which blocks any other transaction trying to do the same. And two threads inserting records with the same primary key value blocks the second till the first finishes. Both of these were triggered by users double clicking links in our web app and were fixed by a better implementation. Perhaps something like that is causing what you are seeing. -Chris On Friday 16 April 2004 11:46 am, Mike Nolan wrote: > > We have a web app with a postgres backend. Most queries have subsecond > > response times through the web even with high usage. Every once in > > awhile someone will run either an ad-hoc query or some other long running > > db process. > > Are you sure it is postgres where the delay is occurring? I ask this > because I also have a web-based front end to postgres, and while most of > the time the queries respond in about a second every now and then I see > one that takes much longer, sometimes 10-15 seconds. > > I've seen this behavior on both my development system and on the > production server. > > The same query a while later might respond quickly again. > > I'm not sure where to look for the delay, either, and it is intermittent > enough that I'm not even sure what monitoring techniques to use. > -- > Mike Nolan -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Index Problem?
Josh Berkus wrote: Ron, The emp table has 60 columns, all indexed, about two-thirds are numeric, but they are not affected by this update. The other 50+ columns are updated in the middle of the night and the amount of time that update takes isn't a concern. Well, I'd say that you have an application design problem, but that's not what you asked for help with ;-) Yeah I agree but I'm not allowed to remove those indexes. Late last night I dumped the table, dropped it and re-created it from the dump (on the production server - when no one was looking). When I re-ran the function it took almost 11 minutes, which was pretty much in line with my results from the dev server. Sounds like you need to run a REINDEX on the table -- and after that, dramatically increase your max_fsm_pages, and run lazy VACUUM immediately after the batch update to clean up. On my dev server I increased max_fsm_pages from the default of 2 to 4, increased checkpoint_segments from 3 to 5, and the function ran in about 6-7 minutes which is a nice increase. According to the docs "Annotated postgresql.conf and Global User Configuration (GUC) Guide" on varlena I'll have to re-start postgres for the changes to take effect there (correct?). Also the docs on Varlena show the max_fsm_pages default to be 10,000 but my default was 20,000, looks like that needs updating. Thanks for your help Josh, I'll see after the weekend what the impact the changes will have on the production server. Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: RESOLVED: Re: [PERFORM] Wierd context-switching issue on Xeon
Dirk, > I'm not sure if this semop() problem is still an issue but the database > behaves a bit out of bounds in this situation, i.e. consuming system > resources with semop() calls 95% while tables are locked very often and > longer. It would be helpful to us if you could test this with the indexes disabled on the non-Bigmem system. I'd like to eliminate Bigmem as a factor, if possible. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Enterprise vertical business[EMAIL PROTECTED] and data analysis solutions(415) 752-2387 and database optimization fax 651-9224 utilizing Open Source technology San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Horribly slow hash join
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Combined with the hash aggregate problem I saw (see my other email to > the list), do you think there could be some issue with the performance > of the hash function on FreeBSD 5.2 on AMD64? Yeah, I was wondering about that too. Hard to imagine what though. The hash function should be pretty platform-independent. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Horribly slow hash join
Yes, stats are up to date, and the values should be fairly unique. Combined with the hash aggregate problem I saw (see my other email to the list), do you think there could be some issue with the performance of the hash function on FreeBSD 5.2 on AMD64? I'll post the table you requested someplace you can grab it. On Fri, Apr 16, 2004 at 12:34:11PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Note the time for the hash join step: > > Have you ANALYZEd these tables lately? > > It looks to me like it's hashing on some column that has only a small > number of distinct values, so that the hash doesn't actually help to > avoid comparisons. The planner should know better than to choose such > a plan, but if it's working with obsolete stats ... > > regards, tom lane > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Index Problem?
Ron, > The emp table has 60 columns, all indexed, about two-thirds are numeric, > but they are not affected by this update. The other 50+ columns are > updated in the middle of the night and the amount of time that update > takes isn't a concern. Well, I'd say that you have an application design problem, but that's not what you asked for help with ;-) > Late last night I dumped the table, dropped it and re-created it from > the dump (on the production server - when no one was looking). When I > re-ran the function it took almost 11 minutes, which was pretty much in > line with my results from the dev server. Sounds like you need to run a REINDEX on the table -- and after that, dramatically increase your max_fsm_pages, and run lazy VACUUM immediately after the batch update to clean up. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Index Problem?
I am using postgres 7.4.1 and have a problem with a plpgsql function. When I run the function on the production server it takes approx 33 minutes to run. I dumped the DB and copied it to a similarly configured box and ran the function and it ran in about 10 minutes. Can anyone offer advice on tuning the function or my database? Here are the lengthy, gory details. F u n c t i o n It updates seven columns of a table 1 to 4 times daily. Current data = 42,000 rows, new data = 30,000 rows. CREATE TYPE employeeType AS (empID INTEGER, updateDate DATE, bDate INTEGER, val1 NUMERIC, val2 NUMERIC, val3 NUMERIC, val4 NUMERIC, favNum NUMERIC); CREATE OR REPLACE FUNCTION updateEmployeeData() RETURNS SETOF employeeType AS ' DECLARE rec RECORD; BEGIN FOR rec IN SELECT empID, updateDate, bDate, val1, val2 , val3, val4, favNum FROM newData LOOP RETURN NEXT rec; UPDATE currentData SET val1=rec.val1, val2=rec.val2, val3=rec.val2, val4=rec.val4, favNum=rec.favNum, updateDate=rec.updateDate WHERE empID=rec.empID; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; The emp table has 60 columns, all indexed, about two-thirds are numeric, but they are not affected by this update. The other 50+ columns are updated in the middle of the night and the amount of time that update takes isn't a concern. Late last night I dumped the table, dropped it and re-created it from the dump (on the production server - when no one was looking). When I re-ran the function it took almost 11 minutes, which was pretty much in line with my results from the dev server. D e t a i l s v 7.4.1 Debian stable 1 GB ram shared_buffers = 2048 sort_mem = 1024 SHMMAX 36000 (360,000,000) VACUUM FULL ANALYZE is run every night, and I ran it yesterday between running the function and it made no difference in running time. top shows the postmaster using minimal cpu (0-40%) and miniscule memory. vmstat shows a fair amount of IO (bo=1000->4000). Yesterday on the dev server we upgraded to the 2.6 kernel and unfortunately only noticed a small increase in update time (about one minute). So does anyone have any suggestions for me on speeding this up? Is it the index? The function is run daily during the mid afternoon to early evening and really drags the performance of the server down (it also hosts a web site). Thanks Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Poor performance of group by query
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Anyone have any ideas why this query would be so slow? That seems very bizarre. Would you be willing to send me a dump of the table off-list? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Horribly slow hash join
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Note the time for the hash join step: Have you ANALYZEd these tables lately? It looks to me like it's hashing on some column that has only a small number of distinct values, so that the hash doesn't actually help to avoid comparisons. The planner should know better than to choose such a plan, but if it's working with obsolete stats ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Long running queries degrade performance
> We have a web app with a postgres backend. Most queries have subsecond > response times through the web even with high usage. Every once in awhile > someone will run either an ad-hoc query or some other long running db > process. Are you sure it is postgres where the delay is occurring? I ask this because I also have a web-based front end to postgres, and while most of the time the queries respond in about a second every now and then I see one that takes much longer, sometimes 10-15 seconds. I've seen this behavior on both my development system and on the production server. The same query a while later might respond quickly again. I'm not sure where to look for the delay, either, and it is intermittent enough that I'm not even sure what monitoring techniques to use. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Horribly slow hash join
Note the time for the hash join step: -- Hash Join (cost=357.62..26677.99 rows=93668 width=62) (actual time=741.159..443381.011 rows=49091 loops=1) Hash Cond: ("outer".work_today = "inner".work_units) -> Hash Join (cost=337.11..24784.11 rows=93668 width=54) (actual time=731.374..417188.519 rows=49091 loops=1) Hash Cond: ("outer".work_total = "inner".work_units) -> Seq Scan on email_rank (cost=0.00..22240.04 rows=254056 width=46) (actual time=582.145..1627.759 rows=49091 loops=1) Filter: (project_id = 8) -> Hash (cost=292.49..292.49 rows=17849 width=16) (actual time=148.944..148.944 rows=0 loops=1) -> Seq Scan on rank_tie_overall o (cost=0.00..292.49 rows=17849 width=16) (actual time=0.059..75.984 rows=17849 loops=1) -> Hash (cost=17.81..17.81 rows=1081 width=16) (actual time=8.996..8.996 rows=0 loops=1) -> Seq Scan on rank_tie_today d (cost=0.00..17.81 rows=1081 width=16) (actual time=0.080..4.635 rows=1081 loops=1) Total runtime: 619047.032 ms By comparison: stats=# set enable_hashjoin=false; SET stats=# explain analyze select * from email_rank, rank_tie_overall o, rank_tie_today d WHERE email_rank.work_today = d.work_units AND email_rank.work_total = o.work_units AND email_rank.project_id = :ProjectID; QUERY PLAN - Merge Join (cost=55391.69..56823.23 rows=93668 width=80) (actual time=2705.344..3349.318 rows=49091 loops=1) Merge Cond: ("outer".work_units = "inner".work_today) -> Index Scan using work_units_today on rank_tie_today d (cost=0.00..23.89 rows=1081 width=16) (actual time=0.150..4.874 rows=1081 loops=1) -> Sort (cost=55391.69..55625.86 rows=93668 width=64) (actual time=2705.153..2888.039 rows=49091 loops=1) Sort Key: email_rank.work_today -> Merge Join (cost=45047.64..47656.93 rows=93668 width=64) (actual time=1685.414..2494.342 rows=49091 loops=1) Merge Cond: ("outer".work_units = "inner".work_total) -> Index Scan using work_units_overall on rank_tie_overall o (cost=0.00..361.34 rows=17849 width=16) (actual time=0.122..79.383 rows=17849 loops=1) -> Sort (cost=45047.64..45682.78 rows=254056 width=48) (actual time=1685.228..1866.215 rows=49091 loops=1) Sort Key: email_rank.work_total -> Seq Scan on email_rank (cost=0.00..22240.04 rows=254056 width=48) (actual time=786.515..1289.101 rows=49091 loops=1) Filter: (project_id = 8) Total runtime: 3548.087 ms Even though the second case is only a select, it seems clear that something's wrong... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] query slows down with more accurate stats
On Tue, 2004-04-13 at 15:18, Tom Lane wrote: > Robert Treat <[EMAIL PROTECTED]> writes: > Well, the first problem is why is ANALYZE's estimate of the total row > count so bad :-( ? I suspect you are running into the situation where > the initial pages of the table are thinly populated and ANALYZE > mistakenly assumes the rest are too. That was my thinking, which is somewhat confirmed after a vacuum full on the table; now analyze gives pretty accurate states. Of course the downside is that now the query is consistently slower. > > so i guess i am wondering if there is something I should be doing to > > help get the better plan at the more accurate stats levels and/or why it > > doesn't stick with the original plan (I noticed disabling merge joins > > does seem to push it back to the original plan). > > With the larger number of estimated rows it's figuring the nestloop will > be too expensive. The row estimate for the cl scan went up from 1248 > to 10546, so the estimated cost for the nestloop plan would go to about > 24 units vs 8 for the mergejoin plan. This is obviously off > rather badly when the true runtimes are 1.7 vs 8.1 seconds :-(. > > I think this is an example of a case where we really need better > estimation of nestloop costs --- it's drastically overestimating the > relative cost of the nestloop because it's not accounting for the cache > benefits of the repeated index searches. You could probably force the > nestloop to be chosen by lowering random_page_cost, but that's just a > kluge solution ... the real problem is the model is wrong. > Unfortunately playing with random_page_cost doesn't seem to be enough to get it to favor the nested loop... though setting it down to 2 does help overall. played with index_cpu_tuple_cost a bit but that seemed even less useful. aggravating when you know there is a better plan it could pick but no (clean) way to get it to do so... > I have a to-do item to work on this, and will try to bump up its > priority a bit. > I'll keep an eye out, thanks Tom. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Poor performance of group by query
Anyone have any ideas why this query would be so slow? stats=# explain analyze SELECT work_units, min(raw_rank) AS rank FROM Trank_work_overall GROUP BY work_units; QUERY PLAN --- HashAggregate (cost=1050.12..1085.98 rows=14347 width=16) (actual time=163149.981..163227.758 rows=17849 loops=1) -> Seq Scan on trank_work_overall (cost=0.00..804.41 rows=49141 width=16) (actual time=0.071..328.682 rows=49091 loops=1) Total runtime: 163296.212 ms (3 rows) stats=# \d Trank_work_overall Table "pg_temp_1.trank_work_overall" Column | Type | Modifiers ++--- raw_rank | bigint | work_units | bigint | stats=# FreeBSD fritz.distributed.net 5.2.1-RELEASE FreeBSD 5.2.1-RELEASE #1: Wed Apr 7 18:42:52 CDT 2004 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/FRITZ amd64 The machine is a dual opteron with 4G of memory. The query in question was not hitting the disk at all. PostgreSQL 7.4.2 compiled with -O3. Also, if I set enable_hashagg = false, it runs in less than a second. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Long running queries degrade performance
Hello all, My apologies if this is not the right mailing list to ask this question, but we are wondering about general performance tuning principles for our main db server. We have a web app with a postgres backend. Most queries have subsecond response times through the web even with high usage. Every once in awhile someone will run either an ad-hoc query or some other long running db process. For some reason, it seems that a small number 3-4 of these jobs running in parallel absolutely floors our server. In monitoring the jobs, linux (Kernel 2.4) drops the long running jobs priority, but even so they seem to hog the system resources making subsequent requests for everyone else very slow. Our database at this point is almost entirely processor and memory bound because it isn't too large to fit most of the working data into memory yet. There is generally little disk activity when this occurs. These long running processes are almost always complex select statements, not generally inserts or updates. We continue to monitor and rework the bottlenecks, but what is a little scary to us is how easily the database becomes almost completely unresponsive with several large jobs running, especially since we have a large number of users. And it only takes one user trying to view a page with one of these selects clicking multiple times because it doesn't come back quickly to bring our system to it's knees for hours. We are looking to move to Kernel 2.6 and possibly a dedicated multiprocessor machine for postgres towards the end of this year. But, I am wondering if there is anything we can do now to increase the interactive performance while there are long running selects running as well. Are there ways to adjust the priority of backend processes, or things to tweak to maximize interactive throughput for the quick jobs while the long running ones run in the background? Or if worse comes to worse to actually kill long running processes without taking down the whole db as we have had to do on occasion. Our server is a modest 2.4Ghz P4 with mirrored UW SCSI drives and 1G of memory. The db on disk is around 800M and this machine also hosts our web app, so there is some contention for the processor. Does anyone have any suggestions or thoughts on things we could look at? Is a multiprocessor box the only answer, or are there other things we should be looking at hardware wise. Thank you for your time. -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] query slows down with more accurate stats
Manfred Koizar <[EMAIL PROTECTED]> writes: > If the number of pages is B and the sample size is n, a perfect sampling > method collects a sample where all tuples come from different pages with > probability (in OpenOffice.org syntax): > p = prod from{i = 0} to{n - 1} {{c(B - i)} over {cB - i}} So? You haven't proven that either sampling method fails to do the same. The desired property can also be phrased as "every tuple should be equally likely to be included in the final sample". What we actually have in the case of your revised algorithm is "every page is equally likely to be sampled, and of the pages included in the sample, every tuple is equally likely to be chosen". Given that there are B total pages of which we sample b pages that happen to contain T tuples (in any distribution), the probability that a particular tuple gets chosen is (b/B) * (n/T) assuming that the two selection steps are independent and unbiased. Now b, B, and n are not dependent on which tuple we are talking about. You could argue that a tuple on a heavily populated page is statistically likely to see a higher T when it's part of the page sample pool than a tuple on a near-empty page is likely to see, and therefore there is some bias against selection of the former tuple. But given a sample over a reasonably large number of pages, the contribution of any one page to T should be fairly small and so this effect ought to be small. In fact, because T directly determines our estimate of the total number of tuples in the relation, your experiments showing that the new method gives a reliable tuple count estimate directly prove that T is pretty stable regardless of exactly which pages get included in the sample. So I think this method is effectively unbiased at the tuple level. The variation in probability of selection of individual tuples can be no worse than the variation in the overall tuple count estimate. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already
Just a note, I was trying the cluster command and was short on space. I figured I had enough space for the new table and index. It failed on me twice. The reason is that I noticed for the command to complete, it needed the space of the new table and 2x the space of the new index. It looks like it creates the new table, then a new index. Afterwards it looked like it creates another index in the DB pgsql_tmp. So for me this is an important consideration, since the new index size was about 7GB. I had not anticipated the second index size so that is why it failed. I ended up creating a link of pgsql_tmp to another parttion to successfully complete. Dan. -Original Message- From: Bill Moran [mailto:[EMAIL PROTECTED] Sent: Thursday, April 15, 2004 4:14 PM To: Shea,Dan [CIS] Cc: Postgres Performance Subject: Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already Shea,Dan [CIS] wrote: > Bill, if you had alot of updates and deletions and wanted to optimize your > table, can you just issue the cluster command. > Will the cluster command rewrite the table without the obsolete data that a > vacuum flags or do you need to issue a vacuum first? From the reference docs: "During the cluster operation, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes. "CLUSTER preserves GRANT, inheritance, index, foreign key, and other ancillary information about the table. "Because the optimizer records statistics about the ordering of tables, it is advisable to run ANALYZE on the newly clustered table. Otherwise, the optimizer may make poor choices of query plans." The primary reason CLUSTER exists is to allow you to physically reorder a table based on a key. This should provide a performance improvement if data with the same key is accessed all at once. (i.e. if you do "SELECT * FROM table WHERE key=5" and it returns 100 rows, those 100 rows are guaranteed to be all on the same part of the disk after CLUSTER, thus a performance improvement should result.) Updates and inserts will add data in the next available space in a table with no regard for any keys, and _may_ require running all over the disk to retrieve the data in the previous example query. I doubt if CLUSTER is an end-all optimization tool. The specific reason I suggested it was because the original poster was asking for an easier way to drop/recreate a table (as prior experimentation had shown this to improve performance) I can't think of anything easier than "CLUSTER ON " Since CLUSTER recreates the table, it implicitly removes the dead tuples. However, it's going to be a LOT slower than vacuum, so if dead tuples are the main problem, vacuum is still the way to go. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: RESOLVED: Re: [PERFORM] Wierd context-switching issue on Xeon
=?ISO-8859-1?Q?Dirk_Lutzeb=E4ck?= <[EMAIL PROTECTED]> writes: > This was the key to look at: we were missing all indices on table which > is used heavily and does lots of locking. After recreating the missing > indices the production system performed normal. No, more excessive > semop() calls, load way below 1.0, CS over 20.000 very rare, more in > thousands realm and less. Hmm ... that's darn interesting. AFAICT the test case I am looking at for Josh's client has no such SQL-level problem ... but I will go back and double check ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
RESOLVED: Re: [PERFORM] Wierd context-switching issue on Xeon
Tom, Josh, I think we have the problem resolved after I found the following note from Tom: > A large number of semops may mean that you have excessive contention on some lockable > resource, but I don't have enough info to guess what resource. This was the key to look at: we were missing all indices on table which is used heavily and does lots of locking. After recreating the missing indices the production system performed normal. No, more excessive semop() calls, load way below 1.0, CS over 20.000 very rare, more in thousands realm and less. This is quite a relief but I am sorry that the problem was so stupid and you wasted some time although Tom said he had also seem excessive semop() calls on another Dual XEON system. Hyperthreading was turned off so far but will be turned on again the next days. I don't expect any problems then. I'm not sure if this semop() problem is still an issue but the database behaves a bit out of bounds in this situation, i.e. consuming system resources with semop() calls 95% while tables are locked very often and longer. Thanks for your help, Dirk At last here is the current vmstat 1 excerpt where the problem has been resolved: procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 1 0 2308 232508 201924 697653200 136 464 628 812 5 1 94 0 0 0 2308 232500 201928 69766280096 296 495 484 4 0 95 0 0 1 2308 232492 201928 697662800 0 176 347 278 1 0 99 0 0 0 2308 233484 201928 69765960040 580 443 351 8 2 90 0 1 0 2308 233484 201928 69766960076 692 792 651 9 2 88 0 0 0 2308 233484 201928 697669600 020 13234 0 0 100 0 0 0 2308 233484 201928 697669600 076 17790 0 0 100 0 0 1 2308 233484 201928 697669600 0 216 321 250 4 0 96 0 0 0 2308 233484 201928 697669600 0 116 417 240 8 0 92 0 0 0 2308 233484 201928 69767840048 600 403 270 8 0 92 0 0 0 2308 233464 201928 69768600076 452 1064 2611 14 1 84 0 0 0 2308 233460 201932 69769000032 256 587 587 12 1 87 0 0 0 2308 233460 201932 69769320032 188 379 287 5 0 94 0 0 0 2308 233460 201932 697693200 0 0 103 8 0 0 100 0 0 0 2308 233460 201932 697693200 0 0 10214 0 0 100 0 0 1 2308 233444 201948 697693200 0 348 300 180 1 0 99 0 1 0 2308 233424 201948 69769480016 380 739 906 4 2 93 0 0 0 2308 233424 201948 69770320068 260 724 987 7 0 92 0 0 0 2308 231924 201948 69771280096 344 1130 753 11 1 88 0 1 0 2308 231924 201948 697724800 112 324 687 628 3 0 97 0 0 0 2308 231924 201948 697724800 0 192 575 430 5 0 95 0 1 0 2308 231924 201948 697724800 0 264 208 124 0 0 100 0 0 0 2308 231924 201948 69772640016 272 380 230 3 2 95 0 0 0 2308 231924 201948 697726400 0 0 104 8 0 0 100 0 0 0 2308 231924 201948 697726400 048 25892 1 0 99 0 0 0 2308 231816 201948 697748400 212 268 456 384 2 0 98 0 0 0 2308 231816 201948 697748400 088 453 770 0 0 99 0 0 0 2308 231452 201948 697768000 196 476 615 676 5 0 94 0 0 0 2308 231452 201948 697768000 0 228 431 400 2 0 98 0 0 0 2308 231452 201948 697768000 0 0 23758 3 0 97 0 0 0 2308 231448 201952 697768000 0 0 36584 2 0 97 0 0 0 2308 231448 201952 697768000 040 246 108 1 0 99 0 0 0 2308 231448 201952 6960096 352 606 1026 4 2 94 0 0 0 2308 231448 201952 69600 0 240 295 266 5 0 95 0 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] query slows down with more accurate stats
On Thu, 15 Apr 2004 20:18:49 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >> getting several tuples from the same page is more likely >> than with the old method. > >Hm, are you sure? Almost sure. Let's look at a corner case: What is the probability of getting a sample with no two tuples from the same page? To simplify the problem assume that each page contains the same number of tuples c. If the number of pages is B and the sample size is n, a perfect sampling method collects a sample where all tuples come from different pages with probability (in OpenOffice.org syntax): p = prod from{i = 0} to{n - 1} {{c(B - i)} over {cB - i}} or in C: p = 1.0; for (i = 0; i < n; ++i) p *= c*(B - i) / (c*B - i) This probability grows with increasing B. >Also, I'm not at all sure that the old method satisfies that constraint >completely in the presence of nonuniform numbers of tuples per page, >so we'd not necessarily be going backwards anyhow ... Yes, it boils down to a decision whether we want to replace one not quite perfect sampling method with another not quite perfect method. I'm still working on putting together the pros and cons ... Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already
I am running an update on the same table update rfis set inquiry_status='APPROVED' where inquiry_status='a'; Its running for past 20 mins. and top output is below. The PID which is executing the query above is 6712. Can anyone tell me why it is in an uninterruptable sleep and does it relate to the apparent poor performance? Is it problem with the disk hardware. I know at nite this query will run reasonably fast. I am running on a decent hardware . Regds mallah. 1:41pm up 348 days, 21:10, 1 user, load average: 11.59, 13.69, 11.49 85 processes: 83 sleeping, 1 running, 0 zombie, 1 stopped CPU0 states: 8.1% user, 2.3% system, 0.0% nice, 89.0% idle CPU1 states: 3.3% user, 2.3% system, 0.0% nice, 93.2% idle CPU2 states: 7.4% user, 1.4% system, 0.0% nice, 90.0% idle CPU3 states: 9.3% user, 7.4% system, 0.0% nice, 82.2% idle Mem: 2064796K av, 2053964K used, 10832K free, 0K shrd, 22288K buff Swap: 2048244K av, 88660K used, 1959584K free 1801532K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 6712 postgres 16 0 86592 84M 83920 D 11.1 4.1 1:36 postmaster 13103 postgres 15 0 54584 53M 52556 S 3.5 2.6 0:01 postmaster 13034 root 16 0 1072 1072 848 R 2.1 0.0 0:02 top 13064 postgres 15 0 67256 65M 64516 D 2.1 3.2 0:01 postmaster 13088 postgres 16 0 43324 42M 40812 D 2.1 2.0 0:00 postmaster 13076 postgres 15 0 49016 47M 46628 S 1.9 2.3 0:00 postmaster 26931 postgres 15 0 84880 82M 83888 S 1.7 4.1 3:52 postmaster 13107 postgres 15 0 18400 17M 16488 S 1.5 0.8 0:00 postmaster 13068 postgres 15 0 44632 43M 42324 D 1.3 2.1 0:00 postmaster 13074 postgres 15 0 68852 67M 66508 D 1.3 3.3 0:00 postmaster 13108 postgres 15 0 11692 11M 10496 S 1.3 0.5 0:00 postmaster 13075 postgres 15 0 50860 49M 47680 S 1.1 2.4 0:04 postmaster 13066 postgres 15 0 56112 54M 53724 S 0.9 2.7 0:01 postmaster 13109 postgres 15 0 14528 14M 13272 S 0.9 0.7 0:00 postmaster 24454 postgres 15 0 2532 2380 1372 S 0.7 0.1 11:58 postmaster 12 root 15 0 0 0 0 SW 0.5 0.0 816:30 bdflush 24455 postgres 15 0 1600 1476 1380 S 0.5 0.0 9:11 postmaster 12528 postgres 15 0 84676 82M 79920 S 0.3 4.0 0:02 postmaster 12575 postgres 15 0 76660 74M 75796 D 0.3 3.7 0:09 postmaster 13038 postgres 15 0 48952 47M 46436 D 0.3 2.3 0:00 postmaster 13069 postgres 15 0 57464 56M 54852 S 0.3 2.7 0:00 postmaster 13102 postgres 15 0 17864 17M 16504 D 0.3 0.8 0:00 postmaster Richard Huxton wrote: On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote: Bill Moran wrote: Rajesh Kumar Mallah wrote: Hi, The problem was solved by reloading the Table. the query now takes only 3 seconds. But that is not a solution. If dropping/recreating the table improves things, then we can reasonably assume that the table is pretty active with updates/inserts. Correct? Yes the table results from an import process and under goes lots of inserts and updates , but thats before the vacuum full operation. the table is not accessed during vacuum. What i want to know is is there any wat to automate the dumping and reload of a table individually. will the below be safe and effective: Shouldn't be necessary assuming you vacuum (not full) regularly. However, looking back at your original posting, the vacuum output doesn't seem to show any rows that need removing. # VACUUM full verbose eyp_rfi; INFO: vacuuming "public.eyp_rfi" INFO: "eyp_rfi": found 0 removable, 505960 nonremovable row versions in 71987 pages DETAIL: 0 dead row versions cannot be removed yet. Since your select count(*) showed 505960 rows, I can't see how dropping/replacing could make a difference on a sequential scan. Since we're not using any indexes I don't see how it could be related to that. begin work; create table new_tab AS select * from tab; truncate table tab; insert into tab select * from new_tab; drop table new_tab; commit; analyze tab; i havenot tried it but plan to do so. but i feel insert would take ages to update the indexes if any. It will have to update them, which will take time. BTW is there any way to disable checks and triggers on a table temporarily while loading data (is updating reltriggers in pg_class safe?) You can take a look at pg_restore and copy how it does it.
Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.
>Josh Berkus > > Treating the optimizer as a black box is something I'm very > used to from > > other RDBMS. My question is, how can you explicitly > re-write a query now > > to "improve" it? If there's no way of manipulating queries without > > actually re-writing the optimizer, we're now in a position where we > > aren't able to diagnose when the optimizer isn't working > effectively. > > Well, there is ... all of the various query cost parameters. They are very blunt instruments for such a delicate task. Surely someone of your experience might have benefit from something more? My feeling is, I would, though I want those tools as *a developer* rather than for tuning specific queries for people, which is always so sensitive to upgrades etc. > But, ultimately, improvements on the planner are still > bottlenecked by having > only one developer actually hacking the changes. > Do we have a clear list of optimizations we'd like to be working on? The TODO items aren't very related to specific optimizations... The only ones I was aware of was deferred subselect evaluation for DBT-3. ...sounds like there's more to discuss here, so I'll duck out now and get back to my current project... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: 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