[PERFORM] stubborn query confuses two different servers
Dear Gurus, Here is this strange query that can't find the optimum plan unless I disable some scan modes or change the costs. (A) is a 2x2.4GHz server with hw raid5 and v7.3.4 database. It chooses hashjoin. (B) is a 300MHz server with 7200rpm ide and v7.4.2 database. It chooses seqscan. If I disable hashjoin/seqscan+hashjoin+mergejoin, both choose index scan. (A) goes from 1000ms to 55ms (B) goes from 5000+ms to 300ms If your expert eyes could catch something missing (an index, analyze or something), I'd be greatly honoured :) Also, tips about which optimizer costs may be too high or too low are highly appreciated. As far as I fumbled with (B), disabling plans step by step got worse until after disabled all tree. Reducing random_page_cost from 2 to 1.27 or lower instantly activated the index scan, but I fear that it hurt most of our other queries. The faster server did not respond to any changes, even with rpc=1 and cpu_index_tuple_cost=0.0001, it chose hash join. All that I discovered is that both servers fail to find the right index (szlltlvl_ttl_szlltlvl) unless forced to. In hope of an enlightening answer, Yours, G. %--- cut here ---% -- QUERY: explain analyze -- 5000msec. rpc1.27-: 300 SELECT coalesce(szallitolevel,0) AS scope_kov_szallitolevel, CASE 'raktros' WHEN 'raktros' THEN szallitolevel_bejovo_e(szallitolevel) WHEN 'sofr' THEN 1027=(SELECT coalesce(sofor,0) FROM szallitolevel WHERE az=szallitolevel) ELSE true END FROM (SELECT l.az AS szallitolevel FROM szallitolevel l, szallitolevel_tetele t WHERE szallitas=1504 AND allapot NOT IN (6,7,8) -- pakoland ttelekkel AND t.szallitolevel = l.az AND NOT t.archiv -- ha archv van, de most nincs, legfljebb kv krben kibukik AND t.fajta IN (4,90,100) GROUP BY t.szallitolevel, l.az HAVING count(t.*)0) t1 NATURAL FULL OUTER JOIN (SELECT szallitolevel, az AS pakolas FROM pakolas WHERE szallitasba=1504 AND sztornozott_pakolas IS NULL) t2 WHERE pakolas IS NULL ORDER BY 2 DESC LIMIT 1; %--- cut here ---% -- plan of (A), hashjoin -- QUERY PLAN Limit (cost=2795.58..2795.58 rows=1 width=12) (actual time=1089.72..1089.72 rows=1 loops=1) - Sort (cost=2795.58..2804.26 rows=3472 width=12) (actual time=1089.72..1089.72 rows=2 loops=1) Sort Key: szallitolevel_bejovo_e(szallitolevel) - Merge Join (cost=2569.48..2591.39 rows=3472 width=12) (actual time=1086.72..1089.67 rows=2 loops=1) Merge Cond: (outer.szallitolevel = inner.szallitolevel) Filter: (inner.az IS NULL) - Sort (cost=1613.43..1614.15 rows=288 width=12) (actual time=1054.21..1054.26 rows=80 loops=1) Sort Key: t1.szallitolevel - Subquery Scan t1 (cost=1572.82..1601.65 rows=288 width=12) (actual time=1050.72..1054.09 rows=80 loops=1) - Aggregate (cost=1572.82..1601.65 rows=288 width=12) (actual time=1050.70..1053.93 rows=80 loops=1) Filter: (count(*) 0) - Group (cost=1572.82..1594.44 rows=2883 width=12) (actual time=1050.64..1052.98 rows=824 loops=1) - Sort (cost=1572.82..1580.03 rows=2883 width=12) (actual time=1050.63..1051.24 rows=824 loops=1) Sort Key: t.szallitolevel, l.az - Hash Join (cost=531.09..1407.13 rows=2883 width=12) (actual time=8.13..1048.89 rows=824 loops=1) Hash Cond: (outer.szallitolevel = inner.az) - Index Scan using szallitolevel_tetele_me on szallitolevel_tetele t (cost=0.00..2.25 rows=167550 width=8) (actual time=0.18..871.77 rows=167888 loops=1) Filter: ((NOT archiv) AND ((fajta = 4) OR (fajta = 90) OR (fajta = 100))) - Hash (cost=530.06..530.06 rows=411 width=4) (actual time=7.92..7.92 rows=0 loops=1) - Index Scan using szlltlvl_szllts on szallitolevel l (cost=0.00..530.06 rows=411 width=4) (actual time=0.04..7.81 rows=92 loops=1) Index Cond: (szallitas = 1504) Filter: ((allapot 6) AND (allapot 7) AND (allapot 8)) - Sort (cost=956.05..964.73 rows=3472 width=8) (actual time=27.80..30.24 rows=3456 loops=1) Sort Key: pakolas.szallitolevel - Index Scan using pakolas_szallitasba on pakolas
[PERFORM] Relation of cpu_*_costs?
Dear Gurus, Please feel free to show me to the archives if my question has already been answered. Last week I fumbled with CPU_TUPLE_COST and revealed that 4 out of 4 tested queries improved by 10-60% if I changed it from 0.01 (default) to 0.40 (ugh). Setting it higher did not bring any improvement. %--- cut here ---% QUESTION1: is there a (theoretical or practical) relation between this one and the other cpu costs? Should I also increase those values by the same rate and find a balance that way? As far as I can guess, there should be a linear relation, i.e. cpu_tuple_cost:cpu_index_tuple_cost:cpu_operator_cost should be a constant ratio, but then again, I suspect there is a cause that they have separate entries in the config file ;) %--- cut here ---% The queries were, or contained, something like: SELECT s.qty FROM a, s WHERE a.id = s.a_id AND a.b_id = 1234; where * a and s are in 1:N relation, * b and a are in 1:N relation, * a.id is pkey in a and b.id is pkey in b. These queries usually return up to 6-10% of the tuples in s (about 16k of 220k) and the planner chose seq scans on s. Disabling seq scan and some other things finally brought up a plan containing index scans that improved two queries. (I tested the other two after I found out the solution of these, to see if they improve or get worse) Also noticed that the largest gain was from the smallest change on cpu_tuple_cost: the query with the largest improvement (to 32% of orig time) chose the better plan from 0.03, but the other one improved (to 79%) only if set cpu_tuple_cost to 0.40 or higher. %--- cut here ---% QUESTION2: am I right setting cpu_tuple_cost, or may there be another cause of poor plan selection? Also tried lowering random_page_cost, but even 1.0 didn't yield any improvement. %--- cut here ---% CONFIGURATION: PostgreSQL 7.3.4, IBM Xeon 2x2.4GHz HT, 5x36GB 10krpm HW RAID-5. We found out quite early that random page cost is quite low (now we have it at 1.5-- maybe it's still to high) and it's true that tasks that require raw cpu power aren't very much faster than PIII-800. Unfortunately I can't test the same hw on 7.4 yet, since it's a production server. TIA, G. %--- cut here ---% \end ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Relation of cpu_*_costs?
Dear Tom, Thanks for your response. - Original Message - From: Tom Lane [EMAIL PROTECTED] Sent: Monday, June 07, 2004 3:51 PM That's pretty hard to believe; particularly on modern machines, I'd think that moving it down would make more sense than moving it up. You're essentially asserting that the CPU time to process one tuple is almost half of the time needed to bring a page in from disk. That is exactly what I had in mind. We found that 5x10krpm HW RAID 5 array blazing fast, while we were really disappointed about CPU. E.g. * tar'ing 600MB took seconds; gzip'ing it took minutes. * initdb ran so fast that I didn't have time to hit Ctrl+C because I forgot a switch ;) * dumping the DB in or out was far faster than adddepend between 7.2 and 7.3 * iirc index scans returning ~26k rows of ~64k were faster than seq scan. (most suspicious case of disk cache) But whatever is the case with my hardware -- could you tell me something (even a search keyword ;) ) about my theoretical question: i.e. relation of cpu_*_costs? I suspect that your test cases were toy cases small enough to be fully cached and thus not incur any actual I/O ... Dunno. The server has 1GB RAM; full DB is ~100MB; largest query was ~7k which moved at least 2 tables of 200k rows and several smaller ones. If it is a toy case for such hw, I humbly accept your opinion. BTW its runtime improved from 53 to 48 sec -- all due to changing cpu tuple cost. I ran the query at different costs, in fast succession: runcostsec #10.0153 #20.4 50 #31.0 48 #41.0 48 #50.4 48 #60.0153 For the second result, I'd say disk cache, yes-- but what about the last result? It's all the same as the first one. Must have been some plan change (I can send the exp-ana results if you wish) G. %--- cut here ---% \end ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] constant vs function param differs in performance
Dear Tom, Thanks for your early response. An addition: the nastier difference increased by adding an index (it was an essential index for this query): func with param improved from 2700ms to 2300ms func with constant improved from 400ms to 31ms inline query improved from 390ms to 2ms So am I reading correct and it is completely normal and can't be helped? (couldn't have tried 7.4 yet) In case it reveals something: --- cut here --- SELECT field FROM (SELECT field, sum(something)=0 AS boolvalue FROM (SELECT * FROM subselect1 NATURAL LEFT JOIN subselect2 UNION SELECT * FROM subselect3 NATURAL LEFT JOIN subselect4 ) AS u GROUP BY field) AS t WHERE not boolvalue ORDER BY simple_sql_func_returns_bool(field) DESC LIMIT 1; --- cut here --- G. - Original Message - From: Tom Lane [EMAIL PROTECTED] Sent: Friday, November 14, 2003 9:59 PM =?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] writes: I have two SQL function that produce different times and I can't understand why. The planner often produces different plans when there are constants in WHERE clauses than when there are variables, because it can get more accurate ideas of how many rows will be retrieved. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] ugly query slower in 7.3, even slower after vacuum full analyze
Dear Gurus, I have a query discussed here earlier that suffers heavily from lack of view flattening in v7.3. Following Tom's guidance, I made a conclusion to that thread (http://archives.postgresql.org/pgsql-performance/2003-05/msg00215.php) and asked it to be confirmed or fixed, but I didn't get any responses. Here are some times, for which I'd like to get some response. Old machine is New machine is * PIII 800, * Dual Xeon 2.4, * IDE 7200, * 5xSCSI 1 HW RAID 5, * psql 7.2.1,* psql 7.3.3, * orig conf * orig and crude conf, as below. * old: 18 sec* new: 24 sec * new w/ vacuum full verbose analyze: 30-31 sec (!!!) 1. Are these times (18 vs 24) believable with such heavy HW change or is there something fishy about it? * I know multiprocessing doesn't come in view with a single query * but cpu and hw speed should * I know 7.3 is slower because of unflattened views 2. What may be the cause of VACUUM slowing the query? 3. Disabling any one of mergejoin, hashjoin, seqscan did no good. Disabling sort prevented query from finishing in several minutes. 4. I have tried to crudely carve optimizer settings as below, but it changed nothing according to this query. Any further ideas? Note that time tests were taken in close succession (test; killall -HUP postmaster; test; ...) If needed, I can attach query, exp-ana outputs before and after vacuum (carved and uncarved conf file), and the vacuum log itself. TIA, G. --- cut here --- shared_bufers = 4096 sort_mem = 4096 effective_cache_size = 2 random_page_cost = 1.5 --- cut here --- ---(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
