Re: [PERFORM] view of view
Keith Worthington wrote: Hi All, I am working on an application that uses PostgreSQL. One of the functions of the application is to generate reports. In order to keep the code in the application simple we create a view of the required data in the database and then simply execute a SELECT * FROM view_of_the_data; All of the manipulation and most of the time even the ordering is handled in the view. My question is how much if any performance degradation is there in creating a view of a view? IOW if I have a view that ties together a couple of tables and manipulates some data what will perform better; a view that filters, manipulates, and orders the data from the first view or a view that performs all the necessary calculations on the original tables? from personal experience, if the inner views contain outer joins performance isn't that great. -- - Rich Doughty ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Strange query plan invloving a view
Tom Lane wrote: Rich Doughty [EMAIL PROTECTED] writes: However, the following query (which i believe should be equivalent) SELECT * FROM tokens.ta_tokenhist h INNER JOIN tokens.ta_tokens t ON h.token_id = t.token_id LEFT JOIN tokens.ta_tokenhist i ON t.token_id = i.token_id AND i.status = 'issued' LEFT JOIN tokens.ta_tokenhist s ON t.token_id = s.token_id AND s.status = 'sold' LEFT JOIN tokens.ta_tokenhist r ON t.token_id = r.token_id AND r.status = 'redeemed' WHERE h.sarreport_id = 9 ; No, that's not equivalent at all, because the implicit parenthesization is left-to-right; therefore you've injected the constraint to a few rows of ta_tokenhist (and therefore only a few rows of ta_tokens) into the bottom of the LEFT JOIN stack. In the other case the constraint is at the wrong end of the join stack, and so the full view output gets formed before anything gets thrown away. Some day the Postgres planner will probably be smart enough to rearrange the join order despite the presence of outer joins ... but today is not that day. thanks for the reply. is there any way i can achieve what i need to by using views, or should i just use a normal query? i'd prefer to use a view but i just can't get round the performance hit. -- - Rich Doughty ---(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
[PERFORM] Strange query plan invloving a view
where ((sarreport_id)::integer = 9). it thinks there are 53430 when in fact there are only 7 (despite a vacuum and analyse). Can anyone give me any suggestions? are the index stats the cause of my problem, or is it the rewrite of the query? Cheers Version: PostgreSQL 8.0.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2 20050821 (prerelease) (Debian 4.0.1-6) -- - Rich Doughty ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Outer join query plans and performance
Tom Lane wrote: Rich Doughty [EMAIL PROTECTED] writes: Tom Lane wrote: The reason these are different is that the second case constrains only the last-to-be-joined table, so the full cartesian product of t and h1 has to be formed. If this wasn't what you had in mind, you might be able to rearrange the order of the LEFT JOINs, but bear in mind that in general, changing outer-join ordering changes the results. (This is why the planner won't fix it for you.) FWIW mysql 4.1 (and i'm no fan at all of mysql) completes both these queries in approximately 3 seconds. Does mysql get the correct answer, though? It's hard to see how they do this fast unless they (a) are playing fast and loose with the semantics, or (b) have very substantially more analysis logic for OUTER JOIN semantics than we do. Perhaps mysql 5.x is better about this sort of thing, but for 4.x I'd definitely find theory (a) more plausible than (b). i would assume so. i'll re-run my testcase later and verify the results of the two side-by-side. The cases that would be interesting are those where rearranging the outer join order actually does change the correct answer --- it may not in this particular case, I haven't thought hard about it. It seems fairly likely to me that they are rearranging the join order here, and I'm just wondering whether they have the logic needed to verify that such a transformation is correct. regards, tom lane -- - Rich Doughty ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Outer join query plans and performance
I tried on pgsql-general but got no reply. re-posting here as it's probably the best place to ask I'm having some significant performance problems with left join. Can anyone give me any pointers as to why the following 2 query plans are so different? EXPLAIN SELECT * FROM tokens.ta_tokens t LEFT JOIN tokens.ta_tokenhist h1 ON t.token_id = h1.token_id LEFT JOIN tokens.ta_tokenhist h2 ON t.token_id = h2.token_id WHERE h1.histdate = 'now'; Nested Loop Left Join (cost=0.00..68778.43 rows=2215 width=1402) - Nested Loop (cost=0.00..55505.62 rows=2215 width=714) - Index Scan using idx_tokenhist__histdate on ta_tokenhist h1 (cost=0.00..22970.70 rows=5752 width=688) Index Cond: (histdate = '2005-10-24 13:28:38.411844'::timestamp without time zone) - Index Scan using ta_tokens_pkey on ta_tokens t (cost=0.00..5.64 rows=1 width=26) Index Cond: ((t.token_id)::integer = (outer.token_id)::integer) - Index Scan using fkx_tokenhist__tokens on ta_tokenhist h2 (cost=0.00..5.98 rows=1 width=688) Index Cond: ((outer.token_id)::integer = (h2.token_id)::integer) Performance is fine for this one and the plan is pretty much as i'd expect. This is where i hit a problem. EXPLAIN SELECT * FROM tokens.ta_tokens t LEFT JOIN tokens.ta_tokenhist h1 ON t.token_id = h1.token_id LEFT JOIN tokens.ta_tokenhist h2 ON t.token_id = h2.token_id WHERE h2.histdate = 'now'; Hash Join (cost=1249148.59..9000709.22 rows=2215 width=1402) Hash Cond: ((outer.token_id)::integer = (inner.token_id)::integer) - Hash Left Join (cost=1225660.51..8181263.40 rows=4045106 width=714) Hash Cond: ((outer.token_id)::integer = (inner.token_id)::integer) - Seq Scan on ta_tokens t (cost=0.00..71828.06 rows=4045106 width=26) - Hash (cost=281243.21..281243.21 rows=10504921 width=688) - Seq Scan on ta_tokenhist h1 (cost=0.00..281243.21 rows=10504921 width=688) - Hash (cost=22970.70..22970.70 rows=5752 width=688) - Index Scan using idx_tokenhist__histdate on ta_tokenhist h2 (cost=0.00..22970.70 rows=5752 width=688) Index Cond: (histdate = '2005-10-24 13:34:51.371905'::timestamp without time zone) I would understand if h2 was joined on h1, but it isn't. It only joins on t. can anyone give any tips on improving the performance of the second query (aside from changing the join order manually)? select version(); version -- PostgreSQL 8.0.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2 20050821 (prerelease) (Debian 4.0.1-6) Thanks -- - Rich Doughty ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Outer join query plans and performance
Tom Lane wrote: Rich Doughty [EMAIL PROTECTED] writes: EXPLAIN SELECT * FROM tokens.ta_tokens t LEFT JOIN tokens.ta_tokenhist h1 ON t.token_id = h1.token_id LEFT JOIN tokens.ta_tokenhist h2 ON t.token_id = h2.token_id WHERE h1.histdate = 'now'; EXPLAIN SELECT * FROM tokens.ta_tokens t LEFT JOIN tokens.ta_tokenhist h1 ON t.token_id = h1.token_id LEFT JOIN tokens.ta_tokenhist h2 ON t.token_id = h2.token_id WHERE h2.histdate = 'now'; The reason these are different is that the second case constrains only the last-to-be-joined table, so the full cartesian product of t and h1 has to be formed. If this wasn't what you had in mind, you might be able to rearrange the order of the LEFT JOINs, but bear in mind that in general, changing outer-join ordering changes the results. (This is why the planner won't fix it for you.) FWIW mysql 4.1 (and i'm no fan at all of mysql) completes both these queries in approximately 3 seconds. postgres does the first in 6 seconds and the second in a lot longer (eventually abandoned). -- - Rich Doughty ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
