Re: [PERFORM] nice/low priority Query
Tobias Brox wrote: [Tobias Brox - Tue at 06:04:34PM +0200] (...) and at one point in the code I'm even asking the database for explain plan, grepping out the estimated cost number, and referring the user to take contact with the IT-dept if he really needs the report. But I digress. I just came to think about some more dirty tricks I can do. I have turned on stats collection in the configuration; now, if I do: select count(*) from pg_stat_activity where not current_query like 'IDLE%'; or, eventually: select count(*) from pg_stat_activity where not current_query like 'IDLE%' and query_start+'1 second'now(); it will give a hint about how busy the database server is, thus I can eventually let the application sleep and retry if there are any other heavy queries in progress. Or - create a table with an estimated_cost column, when you start a new heavy query, insert that query's cost, then sleep SUM(estimated_cost)/100 secs or something. When the query ends, delete the cost-row. Hmm - actually rather than dividing by 100, perhaps make it a tunable value. -- Richard Huxton Archonet Ltd ---(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: [PERFORM] Planner incorrectly choosing seq scan over index scan
Btw - I tried playing around with some of the other planner cost constants but I wasn't able to get the planner to choose the index scan. It seems like the issue is that the estimated cost for fetching one row from the index (3.02) is a little high in my case. Is there any way that I can adjust that cost estimate? Are there any side effects of doing that? Or is my best solution to simple set enable_hashjoin to off for this query? Thanks, MeeteshOn 8/2/05, Meetesh Karia [EMAIL PROTECTED] wrote: Thanks Tom, That modifies the query plan slightly, but the planner still decides to do a hash join for the lte_user table aliased 't'. Though, if I make this change and set enable_hashjoin to off, the query plan (and execution time) gets even better. enable_hashjoin = on -- QUERY PLAN Sort (cost=10113.35..10122.02 rows=3467 width=48) (actual time=1203.000..1203.000 rows=3467 loops=1) Sort Key: c.sourceid, c.targetid - Nested Loop (cost=8711.19..9909.50 rows=3467 width=48) (actual time=1156.000..1203.000 rows=3467 loops=1) - Index Scan using lte_user_pkey on lte_user s (cost=0.00..3.02 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (617004 = user_id) - Hash Join (cost=8711.19..9776.46 rows=3467 width=40) (actual time=1156.000..1187.000 rows=3467 loops=1) Hash Cond: (outer.targetid = inner.user_id) - Seq Scan on candidates617004 c (cost=0.00..76.34 rows=3467 width=32) (actual time=0.000..16.000 rows=3467 loops=1) Filter: (sourceid = 617004) - Hash (cost=8012.55..8012.55 rows=279455 width=16) (actual time=1141.000..1141.000 rows=0 loops=1) - Seq Scan on lte_user t (cost=0.00..8012.55 rows=279455 width=16) (actual time=0.000..720.000 rows=279395 loops=1) Total runtime: 1218.000 ms enable_hashjoin = off --- QUERY PLAN Sort (cost=10942.56..10951.22 rows=3467 width=48) (actual time=188.000..188.000 rows=3467 loops=1) Sort Key: c.sourceid, c.targetid - Nested Loop (cost=0.00..10738.71 rows=3467 width=48) (actual time=0.000..188.000 rows=3467 loops=1) - Index Scan using lte_user_pkey on lte_user s (cost=0.00..3.02 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (617004 = user_id) - Nested Loop (cost=0.00..10605.67 rows=3467 width=40) (actual time=0.000..157.000 rows=3467 loops=1) - Seq Scan on candidates617004 c (cost=0.00..76.34 rows=3467 width=32) (actual time=0.000..15.000 rows=3467 loops=1) Filter: (sourceid = 617004) - Index Scan using lte_user_pkey on lte_user t (cost=0.00..3.02 rows=1 width=16) (actual time=0.028..0.037 rows=1 loops=3467) Index Cond: (outer.targetid = t.user_id) Total runtime: 188.000 ms Thanks, MeeteshOn 8/2/05, Tom Lane [EMAIL PROTECTED] wrote: Meetesh Karia [EMAIL PROTECTED] writes: Sure. The lte_user table is just a collection of users. user_id is assigned= uniquely using a sequence. During some processing, we create a candidates= table (candidates617004 in our case). This table is usually a temp table.= sourceid is a user_id (in this case it is always 617004) and targetid is=20 also a user_id (2860 distinct values out of 3467). The rest of the=20 information is either only used in the select clause or not used at all=20 during this processing.If you know that sourceid has only a single value, it'd probably behelpful to call out that value in the query, ie, where ... AND c.sourceId = 617004 ...regards, tom lane
[PERFORM] Is There A Windows Version of Performance Tuning Documents?
I have in my possession some performance tuning documents authored by Bruce Momjian, Josh Berkus, and others. They give good information on utilities to use (like ipcs, sar, vmstat, etc) to evaluate disk, memory, etc. performance on Unix-based systems. Problem is, I have applications running on Windows 2003, and have worked mostly on Unix before. Was wondering if anyone knows where there might be a Windows performance document that tells what to use / where to look in Windows for some of this data. I am thinking that I may not seeing what I need in perfmon or the Windows task manager. Want to answer questions like: How much memory is being used for disk buffer cache? How to I lock shared memory for PostgreSQL (if possible at all)? How to determine if SWAP (esp. page-in) activity is hurting me? Does Windows use a 'unified buffer cache' or not? How do I determine how much space is required to do most of my sorts in RAM? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Is There A Windows Version of Performance Tuning Documents?
Lane Van Ingen wrote: I have in my possession some performance tuning documents authored by Bruce Momjian, Josh Berkus, and others. They give good information on utilities to use (like ipcs, sar, vmstat, etc) to evaluate disk, memory, etc. performance on Unix-based systems. Problem is, I have applications running on Windows 2003, and have worked mostly on Unix before. Was wondering if anyone knows where there might be a Windows performance document that tells what to use / where to look in Windows for some of this data. I am thinking that I may not seeing what I need in perfmon or the Windows task manager. Want to answer questions like: How much memory is being used for disk buffer cache? How to I lock shared memory for PostgreSQL (if possible at all)? How to determine if SWAP (esp. page-in) activity is hurting me? Does Windows use a 'unified buffer cache' or not? How do I determine how much space is required to do most of my sorts in RAM? I don't know of any specific documentation. I would mention the TaskManager as the first place I would look (Ctrl+Shift+Esc, or right click on the task bar). You can customize the columns that it shows in the process view, so you can get an idea if something is paging, how much I/O it is using, etc. I'm sure there are other better tools, but this one is pretty easy to get to, and shows quite a bit. John =:- signature.asc Description: OpenPGP digital signature
