Re: [PERFORM] Guesses on what this NestLoop is for?
On Mon, 27 Oct 2003 15:32:41 -0800, Josh Berkus [EMAIL PROTECTED] wrote: FROM event_types, events LEFT OUTER JOIN ... WHERE events.status = 1 or events.status = 11 and events.event_date '2003-10-27' and events.etype_id = event_types.etype_id and ( ... ); What I can't figure out is what is that inredibly expensive nested loop for? Sorry, I have no answer to your question, but may I ask whether you really want to get presumably 106 output rows for each event with status 1? Or did you mean WHERE (events.status = 1 OR events.status = 11) AND ... Ideas? I'd also try to push that NOT EXISTS condition into the FROM clause: ...LEFT JOIN (SELECT DISTINCT ON (event_id) event_id, mod_date, mod_user FROM event_history ORDER BY event_id, mod_date ) AS eh ON (events.event_id = eh.event_id) ... WHERE ... AND CASE WHEN eh.event_id IS NULL THEN events.mod_user ELSE eh.mod_user END = 562 If mod_user is NOT NULL in event_history, then CASE ... END can be simplified to COALESCE(eh.mod_user, events.mod_user). Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] More info in explain analyze
This has probably been asked before, but I'll re-ask to spark debate on it again. Is there any good reason to not have explain analyze also include information if temporary files will be required on sorts, hashes, etc. during the processing of a query. [Idea being setting your sort_mem won't be purely anecdotal]... maybe include how much space it needed in temp files? something along the lines of: Sort (Cost=1..10) (Actual=1..1000) (Temp Files=5MB) Seeing that and looking at your current sort_mem and seeing it is 4MB you'll have the info you need to get a nice boost by avoiding that spill at a low cost. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(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] Adding foreign key performance
On Tue, 28 Oct 2003, Jeff wrote: On Tue, 28 Oct 2003 09:16:45 -0500 Jeff [EMAIL PROTECTED] wrote: 7.3.4: 328912ms [cpu pegged] 7.4b4: 298383ms [cpu pegged] Just loaded up delicious 7.4b5 and wow... sort_mem 8192: 137038ms [lots of tmp file activity] sort_mem 256000: 83109ms Hmm, 298383 - 83109 (since those are the 256k numbers). Not as much as I'd have hoped, but I'll take a factor of 3. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Adding foreign key performance
John K. Herreshoff wrote: FWIW: I'm fiddling with that right now, and the FK think was quick... a few seconds... the tables in question have 1400 records, 343000 records and 7200 records... I'm running Beta5... Did those tables have analyze statistics? Can you try it without statistics (I think you have to drop the tables to erase the statistics). -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Adding foreign key performance
I'm not sure about the analyze stats... Where would I find that (in postgresql.conf I suppose) I'll go see what I have set up, and get back to you in 30 minutes or less... John. On Tuesday 28 October 2003 13:34, Bruce Momjian wrote: John K. Herreshoff wrote: FWIW: I'm fiddling with that right now, and the FK think was quick... a few seconds... the tables in question have 1400 records, 343000 records and 7200 records... I'm running Beta5... Did those tables have analyze statistics? Can you try it without statistics (I think you have to drop the tables to erase the statistics). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Ignoring index on (A is null), (A is not null) conditions
Hi, suppose, for simplicity, there is a table with index like this: create table TABLE1 ( A integer ); create index TABLE1_A on TABLE1 (A); My question is: why psql (7.3.3) does not use index when filtering by A IS NULL, A IS NOT NULL expressions? In fact, I need to filter by expression ((A is null) or (A const)). Is there a way to filter by this expression using index? Functional index cannot be used (except strange solution with CASE-ing and converting NULL values into some integer constant) -- Index Scan using table1_a on table1 (cost=0.00..437.14 rows=29164 width=4) Index Cond: (a 1000) -- Seq Scan on table1 (cost=0.00..448.22 rows=1 width=4) Filter: (a IS NULL) Seq Scan on table1 (cost=0.00..448.22 rows=30222 width=4) Filter: (a IS NOT NULL) Seq Scan on table1 (cost=0.00..523.77 rows=29164 width=4) Filter: ((a IS NULL) OR (a 1000)) CH ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Adding foreign key performance
That did not take long... about 13 minutes to reload the tables from an *.mdb file, and a second or two for each of the 'alter table foo add foreign key...' lines. I tried to drop a 'referencing' table, and the database would not let me, said that something depended on it ;o) Is there some way to name the foreign key so that it can be dropped later, or is there a way to drop the foreign key using information already in the database? John. On Tuesday 28 October 2003 13:34, Bruce Momjian wrote: John K. Herreshoff wrote: FWIW: I'm fiddling with that right now, and the FK think was quick... a few seconds... the tables in question have 1400 records, 343000 records and 7200 records... I'm running Beta5... Did those tables have analyze statistics? Can you try it without statistics (I think you have to drop the tables to erase the statistics). ---(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] Very Poor Insert Performance
GS == Greg Stark [EMAIL PROTECTED] writes: GS At a guess the foreign key relationships you're enforcing don't GS have indexes to help them. If they do perhaps postgres isn't using GS them. Or, if you do have indexes, they've bloated to be way too big and are overwhelming your shared buffers. Reindex them and see it it helps. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(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] Adding foreign key performance
J == Jeff [EMAIL PROTECTED] writes: J And this leads to the place we'd get a huge benefit: Restoring J backups.. If there were some way to bump up sort_mem while doing J the restore.. things would be much more pleasant. [Although, even There was a rather substantial thread on this about the time when 7.4b1 was released. J better would be to disable FK stuff while restoring a backup and J assume the backup is sane] How we'd go about doing that is the J subject of much debate. If you're restoring from a pg_dump -Fc (compressed dump) it already happens for you. The indexes and foreign keys are not added until the very end, from what I recall. J Perhaps add the functionality to pg_restore? ie, pg_restore -s J 256MB mybackup.db? It would just end up issuing a set J sort_mem=256000.. This was essentially my proposal, though I had better speed enhancement by increasing the number of checkpoint buffers. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(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] Adding foreign key performance
John K. Herreshoff wrote: That did not take long... about 13 minutes to reload the tables from an *.mdb file, and a second or two for each of the 'alter table foo add foreign key...' lines. I tried to drop a 'referencing' table, and the database would not let me, said that something depended on it ;o) Is there some way to name the foreign key so that it can be dropped later, or is there a way to drop the foreign key using information already in the database? You have to use ALTER TABLE DROP CONSTRAINT perhaps. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
