Re: [PERFORM] tricky query
On 6/28/05, John A Meinel [EMAIL PROTECTED] wrote: Actually, if you already have a lower bound, then you can change it to: SELECT t1.id+1 as id_new FROM id_test t1 WHERE t1.id id_min AND NOT EXISTS (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1) ORDER BY t1.id LIMIT 1; This would actually really help performance if you have a large table and then empty entries start late. You can also boost performance by creating a functional index! CREATE UNIQUE INDEX id_test_id1_index ON id_test ((id+1)); ...and then joining two tables and filtering results. PostgreSQL (8.x) will do Merge Full Join which will use both the indexes: SELECT t2.id+1 FROM id_test t1 FULL OUTER JOIN id_test t2 ON (t1.id = t2.id+1) WHERE t1.id IS NULL LIMIT 1; Limit (cost=0.00..1.52 rows=1 width=4) - Merge Full Join (cost=0.00..1523122.73 rows=74 width=4) Merge Cond: (outer.id = (inner.id + 1)) Filter: (outer.id IS NULL) - Index Scan using id_test_pkey on id_test t1 (cost=0.00..18455.71 rows=74 width=4) - Index Scan using id_test_id1_index on id_test t2 (cost=0.00..1482167.60 rows=74 width=4) (6 rows) ...the only drawback is having to keep two indexes instead of just one. But for large tables I think it is really worth it For my test case, the times are (1-100 range with 26 missing rows): NOT EXISTS -- 670ms NOT IN -- 1800ms indexed FULL OUTER -- 267ms Regards, Dawid PS: Does it qualify for General Bits? ;-))) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Postgresql7.4.5 running slow on plpgsql function
[Please copy the mailing list on replies so others can contribute to and learn from the discussion.] On Wed, Jun 29, 2005 at 12:29:42PM +0800, Chun Yit(Chronos) wrote: Yes, the function will delete records from the temporary table every time on each call. if leaving a lot of dead tuples, then how can we solve it? If the function deletes all records from the temporary table then you could use TRUNCATE instead of DELETE. Otherwise you could VACUUM the table between calls to the function (you can't run VACUUM inside a function). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Exclusive lock question
I have been trying to diagnose a performance problem we have been seeing with a postgres application. The performance of the database server is usually quite good but every now and then it slows to a crawl. The output of vmstat does not show excessive CPU usage or disk IO. The output of ps does show that the number of postgres process's that appear to be stuck in some query spikes and in some cases restarting the postgres server is the only way to clear them. While trying to diagnose this problem I ran select * from pg_locks I could understand most of the output but I was wondering what a result like the following indicates relation | database | transaction | pid | mode | granted --+--+-+---+---+- | |26052434 | 29411 | ExclusiveLock | t | |26051641 | 29345 | ExclusiveLock | t | |26052415 | 29519 | ExclusiveLock | t | |26052407 | 29381 | ExclusiveLock | t | |26052432 | 29658 | ExclusiveLock | t When I see the slowdowns there are hundreds of these with no entry for relation or database. Any ideas what is being locked in this case? Emil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
