Re: [PERFORM] INSERT extremely slow with large data sets (fwd)
On Sat, Nov 15, 2003 at 05:13:38AM -0800, George Essig wrote: > > VACUUM ANALYZE will reclaim disk space and update statistics used Strictly speaking, it does not reclaim disk space. It merely marks it as available, assuming you have enough room in your free space map. VACUUM FULL reclaims disk space, i.e. it compacts the data files and returns that space to the operating system. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Backup/restore of pg_statistics
Hi, I understand that it is not possible to occasionally re-plan the queries in a PL/pgSQL function without dropping and re-creating the function. I think it would be useful if the queries in a PL/pgSQL function could be re-planned on-the-fly. When a lot of data has been added/modified and ANALYZE is suitable to run, it would also be a great idea to re-plan the queries used in PL/pgSQL functions. I understand that this is not possible? The only way would be to DROP/CREATE the functions or to use EXECUTE. I don't think EXECUTE is an option, because preparing the queries every time the function is called is in my case not necessary and just a waste of performance. As a work-around, I am forced to, 1. populate the database with a lot of test data, 2. run ANALYZE, 3. and finally, create the PL/pgSQL functions The prepared queries in the functions will now be sufficiently optimized. I don't think this is a nice solution. I also thought of a slightly better solution, but I don't know if it is possible. My idea was to populate the database once and then save the data in pg_statistics generated by ANALYZE to a file. Every time the database needs to be created, the statistics could then be restored thus making the planner produce "future-optimized" queries when the PL/pgSQL functions are created, even though the database is empty. I would greatly appreciate any help/comments. Thank you. Joel Jacobson <[EMAIL PROTECTED]> ---(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] Top n queries and GROUP BY
All, This is a straight SQL question, maybe not appropriate for a performance list, but... I have a simple stock holdings setup: => select * from t1; nam |co | num -+---+-- joe | ibm | 600 abe | ibm | 1500 joe | cisco | 1200 abe | cisco | 800 joe | novell| 500 joe | microsoft | 200 What I would like to see is a Top-n-holdings-by-name", e.g, for n=2: nam | co| num --++- joe | cisco | 1200 joe | ibm| 600 abe | ibm| 1500 abe | cisco | 800 I can get part of the way by using a LIMIT clause in a subquery, e.g, => select 'abe', a.co, a.num from (select co, num from t1 where nam='abe' order by num desc limit 2) as a; ?column? | co | num --+---+-- abe | ibm | 1500 abe | cisco | 800 but I can't figure out a correlated subquery (or GROUP BY arrangement or anything else) that will cycle through the names. I vaguely remember that these kinds or queries are hard to do in standard SQL, but I was hoping that PG, with its extensions... Thanks, Rich Cullingford [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Top n queries and GROUP BY
Rich Cullingford wrote: All, This is a straight SQL question, maybe not appropriate for a performance list, but... I have a simple stock holdings setup: => select * from t1; nam |co | num -+---+-- joe | ibm | 600 abe | ibm | 1500 joe | cisco | 1200 abe | cisco | 800 joe | novell| 500 joe | microsoft | 200 What I would like to see is a Top-n-holdings-by-name", e.g, for n=2: nam | co| num --++- joe | cisco | 1200 joe | ibm| 600 abe | ibm| 1500 abe | cisco | 800 I can get part of the way by using a LIMIT clause in a subquery, e.g, => select 'abe', a.co, a.num from (select co, num from t1 where nam='abe' order by num desc limit 2) as a; ?column? | co | num --+---+-- abe | ibm | 1500 abe | cisco | 800 but I can't figure out a correlated subquery (or GROUP BY arrangement or anything else) that will cycle through the names. I vaguely remember that these kinds or queries are hard to do in standard SQL, but I was hoping that PG, with its extensions... I forgot about row subqueries; for n=3, for example: => SELECT * FROM t1 WHERE (nam,co,num) IN (SELECT nam,co,num FROM t1 b where b.nam=t1.nam order by num desc limit 3) order by nam, num desc; nam | co | num -++-- abe | ibm| 1500 abe | cisco | 800 joe | cisco | 1200 joe | ibm| 600 joe | novell | 500 (5 rows) Seems to work... Thanks all, Rich Cullingford [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] duration logging setting in 7.4
Hi. I'm trying to set run-time environment in pgsql7.4 so, that it prints all statements with duration time, but I can't understand why setting log_min_duration_statement to '0' causes printing to syslog plenty of lines ending with 'duration: statement:', i.e. without any statement string (except expected ones). Can anybody help me? Richard. -- "First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] duration logging setting in 7.4
Ryszard Lach wrote: > > Hi. > > I'm trying to set run-time environment in pgsql7.4 so, that it prints > all statements with duration time, but I can't understand why setting > log_min_duration_statement to '0' causes printing to syslog plenty of > lines ending with 'duration: statement:', i.e. without any statement > string (except expected ones). Can anybody help me? Can you show us some of the log file? If I do: test=> set log_min_duration_statement = 0; SET test=> select 1; ?column? -- 1 (1 row) I get: LOG: duration: 0.861 ms statement: select 1; -- 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 6: Have you searched our list archives? http://archives.postgresql.org
