[PERFORM] Immutable table functions
Hello guys, I don't know if this is the correct list. Correct me if I'm wrong. I have a directed graph, or better, a tree in postgresql 8.3. One table are the nodes and another one are the connections. Given any node, I need to get all nodes down to it(to the leafs) that have relation with anotherTable. Also, this connection change on time, so I have a connectionDate and a disconnectionDate for each connection (which might be null to represent open interval). This way, I wrote a pgsql function (I rename the tables and columns to generic names). These are the tables and the function: CREATE TABLE node ( id_node integer NOT NULL, CONSTRAINT node_pkey PRIMARY KEY (id_node) ); CREATE TABLE anotherTable ( id_anotherTable integer NOT NULL, id_node integer NOT NULL, CONSTRAINT anothertable_pkey PRIMARY KEY (id_anotherTable), CONSTRAINT anothertable_node_fkey FOREIGN KEY (id_node) REFERENCES node. id_node MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); CREATE TABLE connection ( id_connection integer NOT NULL, down integer NOT NULL, up integer NOT NULL, connectionDate timestamp with time zone, disconnectionDate timestamp with time zone, CONSTRAINT connection_pkey PRIMARY KEY (id_connection), CONSTRAINT down_fkey FOREIGN KEY (down) REFERENCES (id_node) REFERENCES node. id_node MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT up_fkey FOREIGN KEY (up) REFERENCES (id_node) REFERENCES node. id_node MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION); CREATE TABLE observation ( id_observation integer NOT NULL, id_node integer NOT NULL, date timestamp with time zone, CONSTRAINT observation_pkey PRIMARY KEY (id_observation), CONSTRAINT observation_node_fkey FOREIGN KEY (id_node) REFERENCES node. id_node MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); CREATE OR REPLACE FUNCTION get_nodes_related_to_anothertable(integer,timestamp with time zone) RETURNS SETOF integer AS 'DECLARE _id ALIAS FOR $1; _date ALIAS FOR $2; _conn RECORD; BEGIN return query SELECT 1 FROM anothertable WHERE id_node = _id; FOR _ conn IN SELECT * FROM connection c where c.up = _id LOOP if _conn. connectionDate _date then continue; end if; if _conn. disconnectionDate _data then continue; end if; return query SELECT * from get_nodes_related_to_anothertable(_conn.down, _date); END LOOP; END' LANGUAGE 'plpgsql' IMMUTABLE; And I use it on my SELECT: SELECT * FROM (SELECT id_node, date FROM observation ) root_node_obs, node, anotherTable WHERE anotherTable.id_node = node.id_node AND node.id_node IN ( select * from get_nodes_related_to_anothertable(root_node_obs .id_node,root_node_obs .date)); Even with IMMUTABLE on the function, postgresql executes the function many times with the same parameters. In a single run: select * from get_nodes_related_to_anothertable(236,now()); it returns 5 rows and runs in 27ms. But in the bigger SELECT, it take 5s to each observation row (and I may have some :-) ). I know that IN generally is not optimization-friendly but I don't know how to use my function without it. Any clues guys? Thanks,
Re: [PERFORM] Immutable table functions
Luiz Angelo Daros de Luca wrote: I have a directed graph, or better, a tree in postgresql 8.3. One table are the nodes and another one are the connections. Given any node, I need to get all nodes down to it(to the leafs) that have relation with anotherTable. Also, this connection change on time, so I have a connectionDate and a disconnectionDate for each connection (which might be null to represent open interval). This way, I wrote a pgsql function (I rename the tables and columns to generic names). These are the tables and the function: Hello Luiz, If you could upgrade to 8.4, you could use WITH RECURSIVE - my experience is that it is several orders of magnitude faster than recursive functions. http://developer.postgresql.org/pgdocs/postgres/queries-with.html regards, Yeb Havinga -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Almost infinite query - Different Query Plan when changing where clause value
Some informations: The following problem has been detected on Postgresql 8.3 and 8.4. on System linux or windows Default AutoVacuum daemon working One pg_dump every day This happens sometimes and i don't see what can be the cause. A manual Vacuum Analyse repair that problem. Dear you all, Hope someone would help me understand why only changing a where clause value attribute will have a big impact on query plan and lead to almost unending query. regards lionel This is my query: select element2_.element_seqnum as col_0_0_, element1_.element_seqnum as col_1_0_, link0_.link_rank_in_bunch as col_2_0_, element2_.element_state as col_3_0_ from public.link link0_ inner join public.element element1_ on link0_.element_target=element1_.element_seqnum inner join public.user_element users3_ on element1_.element_seqnum=users3_.element_seqnum inner join public.user user4_ on users3_.user_seqnum=user4_.user_seqnum inner join public.element_block blocks7_ on element1_.element_seqnum=blocks7_.element_seqnum inner join public.block block8_ on blocks7_.block_seqnum=block8_.block_seqnum inner join public.element element2_ on link0_.element_source=element2_.element_seqnum inner join public.user_element users5_ on element2_.element_seqnum=users5_.element_seqnum inner join public.user user6_ on users5_.user_seqnum=user6_.user_seqnum inner join public.element_block blocks9_ on element2_.element_seqnum=blocks9_.element_seqnum inner join public.block block10_ on blocks9_.block_seqnum=block10_.block_seqnum where block10_.block_seqnum=5 and block8_.block_seqnum=5 and user6_.user_seqnum=XX and (link0_.link_sup_date is null) and user4_.user_seqnum=XX --- This one works well: Query Plan for that user 2 (user4_.user_seqnum=2 and user6_.user_seqnum=2 ) will be: Nested Loop (cost=36.33..5932.28 rows=1 width=16) - Nested Loop (cost=36.33..5926.38 rows=1 width=20) - Nested Loop (cost=36.33..5925.23 rows=1 width=24) Join Filter: (link0_.element_source = blocks9_.element_seqnum) - Index Scan using fki_element_block_block on element_block blocks9_ (cost=0.00..8.29 rows=1 width=8) Index Cond: (block_seqnum = 5) - Nested Loop (cost=36.33..5916.64 rows=24 width=28) - Nested Loop (cost=36.33..5883.29 rows=4 width=40) - Seq Scan on user user4_ (cost=0.00..5.89 rows=1 width=4) Filter: (user_seqnum = 2) - Nested Loop (cost=36.33..5877.36 rows=4 width=36) - Nested Loop (cost=36.33..5860.81 rows=4 width=28) - Nested Loop (cost=36.33..5835.59 rows=6 width=20) - Nested Loop (cost=0.00..17.76 rows=1 width=8) - Nested Loop (cost=0.00..16.61 rows=1 width=12) - Index Scan using fki_element_block_block on element_block blocks7_ (cost=0.00..8.29 rows=1 width=8) Index Cond: (block_seqnum = 5) - Index Scan using pk_element on element element1_ (cost=0.00..8.31 rows=1 width=4) Index Cond: (element1_.element_seqnum = blocks7_.element_seqnum) - Seq Scan on block block8_ (cost=0.00..1.14 rows=1 width=4) Filter: (block8_.block_seqnum = 5) - Bitmap Heap Scan on link link0_ (cost=36.33..5792.21 rows=2050 width=12) Recheck Cond: (link0_.element_target = element1_.element_seqnum) Filter: (link0_.link_sup_date IS NULL) - Bitmap Index Scan on element_target_fk (cost=0.00..35.82 rows=2050 width=0) Index Cond: (link0_.element_target = element1_.element_seqnum) - Index Scan using pk_user_element on user_element users5_ (cost=0.00..4.19 rows=1 width=8) Index Cond: ((users5_.user_seqnum = 2) AND (users5_.element_seqnum = link0_.element_source)) - Index Scan using pk_element on element element2_ (cost=0.00..4.12 rows=1 width=8) Index Cond: (element2_.element_seqnum = link0_.element_source) - Index Scan using pk_user_element on user_element users3_ (cost=0.00..8.33 rows=1 width=8) Index Cond:
Re: [PERFORM] moving pg_xlog -- yeah, it's worth it!
Kevin Grittner wrote: Hannu Krosing wrote: Can it be, that each request does at least 1 write op (update session or log something) ? Well, the web application connects through a login which only has SELECT rights; but, in discussing a previous issue we've pretty well established that it's not unusual for a read to force a dirty buffer to write to the OS. Perhaps this is the issue here again. Nothing is logged on the database server for every request. I don't think it explains it, because dirty buffers are obviously written to the data area, not pg_xlog. I wonder if it might also pay to make the background writer even more aggressive than we have, so that SELECT-only queries don't spend so much time writing pages. That's worth trying. Anyway, given that these are replication targets, and aren't the database of origin for any data of their own, I guess there's no reason not to try asynchronous commit. Yeah; since the transactions only ever write commit records to WAL, it wouldn't matter a bit that they are lost on crash. And you should see an improvement, because they wouldn't have to flush at all. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] moving pg_xlog -- yeah, it's worth it!
Alvaro Herrera wrote: Kevin Grittner wrote: Anyway, given that these are replication targets, and aren't the database of origin for any data of their own, I guess there's no reason not to try asynchronous commit. Yeah; since the transactions only ever write commit records to WAL, it wouldn't matter a bit that they are lost on crash. And you should see an improvement, because they wouldn't have to flush at all. Actually, a transaction that performed no writes doesn't get a commit WAL record written, so it shouldn't make any difference at all. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Almost infinite query - Different Query Plan when changing where clause value
lionel duboeuf lionel.dubo...@boozter.com wrote: Some informations: The following problem has been detected on Postgresql 8.3 and 8.4. on System linux or windows Default AutoVacuum daemon working One pg_dump every day This happens sometimes and i don't see what can be the cause. A manual Vacuum Analyse repair that problem. It's good to know that the issue has been observed in more than one release or on more than one platform, but it's also useful to get a bit more information about one particular occurrence. Please read this: http://wiki.postgresql.org/wiki/SlowQueryQuestions In particular, knowing such things as your postgresql.conf settings, the disk configuration, how much RAM the machine has, etc. can allow us to provide more useful advice. Please run these as EXPLAIN ANALYZE (or at least whatever you can get to finish that way) instead of just EXPLAIN. If you can let the slow one run through EXPLAIN ANALYZE overnight or on a test machine so that it can complete, it will give us a lot more with which to work. Please attach wide output (like that from EXPLAIN) as a text attachment, to prevent wrapping which makes it hard to read. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Bryce Nesbitt bry...@obviously.com wrote: I've got a very slow query, which I can make faster by doing something seemingly trivial. Out of curiosity, what kind of performance do you get with?: EXPLAIN ANALYZE SELECT contexts.context_key FROM contexts JOIN articles ON (articles.context_key = contexts.context_key) JOIN matview_82034 ON (matview_82034.context_key = contexts.context_key) WHERE EXISTS ( SELECT * FROM article_words JOIN words using (word_key) WHERE context_key = contexts.context_key AND word = 'insider' ) AND EXISTS ( SELECT * FROM article_words JOIN words using (word_key) WHERE context_key = contexts.context_key AND word = 'trading' ) AND EXISTS ( SELECT * FROM virtual_ancestors a JOIN bp_categories ON (bp_categories.context_key = a.ancestor_key) WHERE a.context_key = contexts.context_key AND lower(bp_categories.category) = 'law' ) AND articles.indexed ; (You may have to add some table aliases in the subqueries.) If you are able to make a copy on 8.4 and test the various forms, that would also be interesting. I suspect that the above might do pretty well in 8.4. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Exists can be quite slow. So can not exists See if you can re-write it using a sub-select - just replace the exists with (select ...) is not null Surprisingly this often results in a MUCH better query plan under Postgresql. Why the planner evaluates it better eludes me (it shouldn't) but the differences are often STRIKING - I've seen factor-of-10 differences in execution performance. Kevin Grittner wrote: Bryce Nesbitt bry...@obviously.com wrote: I've got a very slow query, which I can make faster by doing something seemingly trivial. Out of curiosity, what kind of performance do you get with?: EXPLAIN ANALYZE SELECT contexts.context_key FROM contexts JOIN articles ON (articles.context_key = contexts.context_key) JOIN matview_82034 ON (matview_82034.context_key = contexts.context_key) WHERE EXISTS ( SELECT * FROM article_words JOIN words using (word_key) WHERE context_key = contexts.context_key AND word = 'insider' ) AND EXISTS ( SELECT * FROM article_words JOIN words using (word_key) WHERE context_key = contexts.context_key AND word = 'trading' ) AND EXISTS ( SELECT * FROM virtual_ancestors a JOIN bp_categories ON (bp_categories.context_key = a.ancestor_key) WHERE a.context_key = contexts.context_key AND lower(bp_categories.category) = 'law' ) AND articles.indexed ; (You may have to add some table aliases in the subqueries.) If you are able to make a copy on 8.4 and test the various forms, that would also be interesting. I suspect that the above might do pretty well in 8.4. -Kevin attachment: karl.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Karl Denninger k...@denninger.net wrote: Kevin Grittner wrote: I suspect that the above might do pretty well in 8.4. Exists can be quite slow. So can not exists See if you can re-write it using a sub-select - just replace the exists with (select ...) is not null Surprisingly this often results in a MUCH better query plan under Postgresql. Why the planner evaluates it better eludes me (it shouldn't) but the differences are often STRIKING - I've seen factor-of-10 differences in execution performance. Have you seen such a difference under 8.4? Can you provide a self-contained example? -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Questions on plan with INSERT/SELECT on partitioned table
I have been trying to track down a performance issue we've been having with a INSERT INTO ... SELECT query run against a partitioned table on postgres. The problem appears to be in the plan building of the query and after some further research I think I have nailed down a simplified example of the problem. Attached is a simple script that will build an example of our table structure load 2 records and run the explain that produces the plan in question. The query plan looks like the following: QUERY PLAN -- -- Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: false Nested Loop (cost=23.50..47.08 rows=4 width=1036) - Append (cost=0.00..23.50 rows=2 width=520) - Seq Scan on base (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Seq Scan on base_1 base (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Materialize (cost=23.50..23.52 rows=2 width=520) - Append (cost=0.00..23.50 rows=2 width=520) - Seq Scan on another (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Seq Scan on another_1 another (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) Result (cost=23.50..47.08 rows=1 width=1036) One-Time Filter: false - Nested Loop (cost=23.50..47.08 rows=1 width=1036) - Append (cost=0.00..23.50 rows=2 width=520) - Seq Scan on base (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Seq Scan on base_1 base (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Materialize (cost=23.50..23.52 rows=2 width=520) - Append (cost=0.00..23.50 rows=2 width=520) - Seq Scan on another (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Seq Scan on another_1 another (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) Result (cost=23.50..47.08 rows=1 width=1036) One-Time Filter: false - Nested Loop (cost=23.50..47.08 rows=1 width=1036) - Append (cost=0.00..23.50 rows=2 width=520) - Seq Scan on base (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Seq Scan on base_1 base (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Materialize (cost=23.50..23.52 rows=2 width=520) - Append (cost=0.00..23.50 rows=2 width=520) - Seq Scan on another (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) - Seq Scan on another_1 another (cost=0.00..11.75 rows=1 width=520) Filter: (id = 1) (45 rows) The problem appears to be the multiple Result sections. I don't understand why this is happening but I do know that a new results section occurs for each new partition you add. The result is that in my actual system where we have a couple hundred partitions this query takes minutes to plan. I've tried this on a Dell (24 core 2.66 GHz) with 192 GB of RAM running postgres 8.3.7 and an IBM 570 (16 core 1.6 Ghz Power 5) with 16 GB of RAM running postgres 8.4.2 both running RedHat Enterprise 5.0 and both take what I would consider way to long to generate the plan. The 8.3.7 version has constraint exclusion on and the 8.4.2 version has constraint exclusion partial. test.sql Description: test.sql -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your
Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Yes: select forum, * from post where marked is not true and toppost = 1 and (select login from ignore_thread where login='xxx' and number=post.number) is null and (replied now() - '30 days'::interval) and (replied (select lastview from forumlog where login='xxx' and forum=post.forum and number is null)) is not false and (replied (select lastview from forumlog where login='xxx' and forum=post.forum and number=post.number)) is not false and ((post.forum = (select who from excludenew where who='xxx' and forum_name = post.forum)) or (select who from excludenew where who='xxx' and forum_name = post.forum) is null) order by pinned desc, replied desc offset 0 limit 100 Returns the following query plan: QUERY PLAN - Limit (cost=5301575.63..5301575.63 rows=1 width=433) (actual time=771.000..771.455 rows=100 loops=1) - Sort (cost=5301575.63..5301575.63 rows=1 width=433) (actual time=770.996..771.141 rows=100 loops=1) Sort Key: post.pinned, post.replied Sort Method: top-N heapsort Memory: 120kB - Index Scan using post_top on post (cost=0.00..5301575.62 rows=1 width=433) (actual time=0.664..752.994 rows=3905 loops=1) Index Cond: (toppost = 1) Filter: ((marked IS NOT TRUE) AND (replied (now() - '30 days'::interval)) AND ((SubPlan 1) IS NULL) AND ((replied (SubPlan 2)) IS NOT FALSE) AND ((replied (SubPlan 3)) IS NOT FALSE) AND ((forum = (SubPlan 4)) OR ((SubPlan 5) IS NULL))) SubPlan 1 - Seq Scan on ignore_thread (cost=0.00..5.45 rows=1 width=7) (actual time=0.037..0.037 rows=0 loops=3905) Filter: ((login = 'xxx'::text) AND (number = $0)) SubPlan 2 - Index Scan using forumlog_composite on forumlog (cost=0.00..9.50 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=3905) Index Cond: ((login = 'xxx'::text) AND (forum = $1) AND (number IS NULL)) SubPlan 3 - Index Scan using forumlog_composite on forumlog (cost=0.00..9.50 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=3905) Index Cond: ((login = 'xxx'::text) AND (forum = $1) AND (number = $0)) SubPlan 4 - Index Scan using excludenew_pkey on excludenew (cost=0.00..8.27 rows=1 width=9) (actual time=0.004..0.004 rows=0 loops=3905) Index Cond: ((who = 'xxx'::text) AND (forum_name = $1)) SubPlan 5 - Index Scan using excludenew_pkey on excludenew (cost=0.00..8.27 rows=1 width=9) (actual time=0.004..0.004 rows=0 loops=3905) Index Cond: ((who = 'xxx'::text) AND (forum_name = $1)) Total runtime: 771.907 ms (23 rows) The alternative: select forum, * from post where marked is not true and toppost = 1 and not exists (select login from ignore_thread where login='xxx' and number=post.number) and (replied now() - '30 days'::interval) and (replied (select lastview from forumlog where login='xxx' and forum=post.forum and number is null)) is not false and (replied (select lastview from forumlog where login='xxx' and forum=post.forum and number=post.number)) is not false and ((post.forum = (select who from excludenew where who='xxx' and forum_name = post.forum)) or (select who from excludenew where who='xxx' and forum_name = post.forum) is null) order by pinned desc, replied desc offset 0 limit 100 goes nuts. (Yes, I know, most of those others which are not false could be Exists too) Explain Analyze on the alternative CLAIMS the same query planner time (within a few milliseconds) with explain analyze. But if I replace the executing code with one that has the alternative (not exists) syntax in it, the system load goes to crap instantly and the execution times in the wild go bananas. I don't know why it does - I just know THAT it does. When I first added that top clause in there (to allow people an individual ignore thread list) the system load went bananas immediately and forced me to back it out. When I re-wrote the query as above the performance was (and remains) fine. I'm running 8.4.2. I agree (in advance) it shouldn't trash performance - all I know is that it does and forced me to re-write the query. Kevin Grittner wrote: Karl Denninger k...@denninger.net wrote: Kevin Grittner wrote: I suspect that the above might do pretty well in 8.4. Exists can be quite slow. So can not exists
Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Karl Denninger k...@denninger.net wrote: Kevin Grittner wrote: Have you seen such a difference under 8.4? Can you provide a self-contained example? Yes: [query and EXPLAIN ANALYZE of fast query] The alternative: [query with no other information] goes nuts. Which means what? Could you post an EXPLAIN ANALYZE, or at least an EXPLAIN, of the slow version? Can you post the table structure, including indexes? -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Questions on plan with INSERT/SELECT on partitioned table
Connors, Bill bconn...@rochgrp.com writes: ... in my actual system where we have a couple hundred partitions this query takes minutes to plan. Please note what the documentation says under Partitioning Caveats. The current partitioning support is not meant to scale past a few dozen partitions. So the solution to your problem is to not have so many partitions. There are plans to make some fundamental changes in partitioning support, and one of the main reasons for that is to allow it to scale to larger numbers of partitions. This is just in the arm-waving stage though ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Dell PERC H700/H800
I do think it's valid to prevent idiot customers from installing drives that use too much power or run too hot, or desktop drives that don't support fast-fail reads, thus driving up Dell's support load, but it sounds like this is more of a lock-in attempt. This is kind of a dumb move on their part most enterprise buyers will buy drives through them anyway for support reasons, and the low end guys who are price sensitive will just take their business elsewhere. I'm not sure who thought this would increase revenue materially. Cheers Dave On Thu, Feb 11, 2010 at 3:55 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Thu, Feb 11, 2010 at 1:11 PM, James Mansion ja...@mansionfamily.plus.com wrote: Matthew Wakeling wrote: Just a heads up - apparently the more recent Dell RAID controllers will no longer recognise hard discs that weren't sold through Dell. http://www.channelregister.co.uk/2010/02/10/dell_perc_11th_gen_qualified_hdds_only/ As one of the comments points out, that kind of makes them no longer SATA or SAS compatible, and they shouldn't be allowed to use those acronyms any more. Matthew I think that's potentially FUD. Its all about 'Dell qualified drives'. I can't see anything that suggests that Dell will OEM drives and somehow tag them so that the drive must have come from them. Of course they are big enough that they could have special BIOS I guess, but I read it that the drive types (and presumably revisions thereof) had to be recognised by the controller from a list, which presumably can be reflashed, which is not quite saying that if some WD enterprise drive model is 'qualified' then you have to buy it from Dell.. Do you have any further detail? In the post to the dell mailing list ( http://lists.us.dell.com/pipermail/linux-poweredge/2010-February/041335.html ) It was pointed out that the user had installed Seagate ES.2 drives, which are enterprise class drives that have been around a while and are kind of the standard SATA enterprise clas drives and are listed so by Seagate: http://www.seagate.com/www/en-us/products/servers/barracuda_es/barracuda_es.2 These drives were marked as BLOCKED and unusable by the system. The pdf linked to in the dell forum specifically states that the hard drives are loaded with a dell specific firmware. The PDF seems otherwise free of useful information, and is mostly a marketing tool as near as I can tell. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Karl Denninger k...@denninger.net writes: Explain Analyze on the alternative CLAIMS the same query planner time (within a few milliseconds) with explain analyze. But if I replace the executing code with one that has the alternative (not exists) syntax in it, the system load goes to crap instantly and the execution times in the wild go bananas. Could we see the actual explain analyze output, and not some handwaving? What I would expect 8.4 to do with the NOT EXISTS version is to convert it to an antijoin --- probably a hash antijoin given that the subtable is apparently small. That should be a significant win compared to repeated seqscans as you have now. The only way I could see for it to be a loss is that that join would probably be performed after the other subplan tests instead of before. However, the rowcounts for your original query suggest that all the subplans get executed the same number of times; so at least on the test values you used here, all those conditions succeed. Maybe your test values were not representative of in the wild cases, and in the real usage it's important to make this test before the others. If that's what it is, you might see what happens when all of the sub-selects are converted to exists/not exists style, instead of having a mishmash... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] moving pg_xlog -- yeah, it's worth it!
Kevin Grittner wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: Actually, a transaction that performed no writes doesn't get a commit WAL record written, so it shouldn't make any difference at all. Well, concurrent to the web application is the replication. Would asynchronous commit of that potentially alter the pattern of writes such that it had less impact on the reads? Well, certainly async commit would completely change the pattern of writes: it would give the controller an opportunity to reorder them according to some scheduler. Otherwise they are strictly serialized. I'm thinking, again, of why the placement of the pg_xlog on a separate file system made such a dramatic difference to the read-only response time -- might it make less difference if the replication was using asynchronous commit? Yeah, I think it would have been less notorious, but this is all theoretical. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Almost infinite query - Different Query Plan when changing where clause value
Thanks kevin for your answer. Here is some additionnal informations attached as files. regards. Lionel Kevin Grittner a écrit : lionel duboeuf lionel.dubo...@boozter.com wrote: Some informations: The following problem has been detected on Postgresql 8.3 and 8.4. on System linux or windows Default AutoVacuum daemon working One pg_dump every day This happens sometimes and i don't see what can be the cause. A manual Vacuum Analyse repair that problem. It's good to know that the issue has been observed in more than one release or on more than one platform, but it's also useful to get a bit more information about one particular occurrence. Please read this: http://wiki.postgresql.org/wiki/SlowQueryQuestions In particular, knowing such things as your postgresql.conf settings, the disk configuration, how much RAM the machine has, etc. can allow us to provide more useful advice. Please run these as EXPLAIN ANALYZE (or at least whatever you can get to finish that way) instead of just EXPLAIN. If you can let the slow one run through EXPLAIN ANALYZE overnight or on a test machine so that it can complete, it will give us a lot more with which to work. Please attach wide output (like that from EXPLAIN) as a text attachment, to prevent wrapping which makes it hard to read. -Kevin processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Core(TM)2 Duo CPU T7500 @ 2.20GHz stepping: 11 cpu MHz : 2201.000 cache size : 4096 KB physical id : 0 siblings: 2 core id : 0 cpu cores : 2 apicid : 0 initial apicid : 0 fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc arch_perfmon pebs bts pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm lahf_lm ida tpr_shadow vnmi flexpriority bogomips: 4389.59 clflush size: 64 power management: processor : 1 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Core(TM)2 Duo CPU T7500 @ 2.20GHz stepping: 11 cpu MHz : 2201.000 cache size : 4096 KB physical id : 0 siblings: 2 core id : 1 cpu cores : 2 apicid : 1 initial apicid : 1 fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc arch_perfmon pebs bts pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm lahf_lm ida tpr_shadow vnmi flexpriority bogomips: 4388.96 clflush size: 64 power management: QUERY PLAN Nested Loop (cost=58.71..1512.50 rows=1 width=16) (actual time=1.849..535681.136 rows=14 loops=1) - Nested Loop (cost=58.71..1506.60 rows=1 width=20) (actual time=1.839..535680.377 rows=14 loops=1) Join Filter: (link0_.element_source = blocks9_.element_seqnum) - Nested Loop (cost=58.71..1498.29 rows=1 width=32) (actual time=1.819..535337.910 rows=6534 loops=1) - Nested Loop (cost=58.71..1497.14 rows=1 width=28) (actual time=1.811..535246.825 rows=6534 loops=1) - Nested Loop (cost=58.71..1488.83 rows=1 width=36) (actual time=1.800..535160.718 rows=6534 loops=1) - Nested Loop (cost=58.71..1484.40 rows=1 width=28) (actual time=1.772..535042.549 rows=6534 loops=1) - Nested Loop (cost=13.20..1434.87 rows=1 width=16) (actual time=1.398..8327.567 rows=122850 loops=1) - Nested Loop (cost=13.20..1428.97 rows=1 width=20) (actual time=1.387..1911.520 rows=122850 loops=1) - Nested Loop (cost=13.20..1427.83 rows=1 width=24) (actual time=1.374..517.662 rows=122850 loops=1) - Nested Loop (cost=0.00..16.63 rows=1 width=16) (actual time=0.085..0.409 rows=15 loops=1) - Index Scan using fki_element_block_block on element_block blocks7_ (cost=0.00..8.30 rows=1 width=8) (actual time=0.045..0.082 rows=15 loops=1) Index Cond: (block_seqnum = 5) - Index Scan using pk_user_element on user_element users3_ (cost=0.00..8.33 rows=1 width=8) (actual time=0.012..0.015 rows=1 loops=15) Index Cond:
Re: [PERFORM] Almost infinite query - Different Query Plan when changing where clause value
lionel duboeuf lionel.dubo...@boozter.com wrote: Thanks kevin for your answer. Here is some additionnal informations attached as files. Could you supply an EXPLAIN ANALYZE of the fast plan as an attachment, for comparison? Anyway, it looks like at least one big problem is the bad estimate on how many rows will be generated by joining to the users5_ table: (cost=13.20..1427.83 rows=1 width=24) (actual time=1.374..517.662 rows=122850 loops=1) If it had expected 122850 rows to qualify for that join, it probably would have picked a different plan. I just reread your original email, and I'm not sure I understand what you meant regarding VACUUM ANALYZE. If you run that right beforehand, do you still get the slow plan for user 10? -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] moving pg_xlog -- yeah, it's worth it!
Kevin Grittner wrote: I wonder if it might also pay to make the background writer even more aggressive than we have, so that SELECT-only queries don't spend so much time writing pages. You can easily quantify if the BGW is aggressive enough. Buffers leave the cache three ways, and they each show up as separate counts in pg_stat_bgwriter: buffers_checkpoint, buffers_clean (the BGW), and buffers_backend (the queries). Cranking it up further tends to shift writes out of buffers_backend, which are the ones you want to avoid, toward buffers_clean instead. If buffers_backend is already low on a percentage basis compared to the other two, there's little benefit in trying to make the BGW do more. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Dell PERC H700/H800
I've been full-on vocally anti-Dell ever since they started releasing PCs with the non-standard ATX power supply pinout; that was my final straw with their terrible quality decisions. But after doing two tuning exercises with PERC6 controllers and getting quite good results this year, just a few weeks ago I begrudgingly added them to my known good hardware list as a viable candidate to suggest to people. They finally took a good LSI card and didn't screw anything up in their version. I am somehow relieved that sanity has returned to my view of the world now, with Dell right back onto the shit list again. If they want a HCL and to warn people they're in an unsupported configuration when they violate it, which happens on some of their equipment, fine. This move is just going to kill sales of their servers into the low-end of the market, which relied heavily on buying the base system from them and then dropping their own drives in rather than pay the full enterprise drive markup for non-critical systems. I do not as a rule ever do business with a vendor who tries to lock me into being their sole supplier, particularly for consumable replacement parts--certainly a category hard drives fall into. Probably the best place to complain and suggest others do the same at is http://www.ideastorm.com/ideaView?id=0877dwTAAQ -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Kevin Grittner wrote: Bryce Nesbitt bry...@obviously.com wrote: I've got a very slow query, which I can make faster by doing something seemingly trivial. Out of curiosity, what kind of performance do you get with?: EXPLAIN ANALYZE SELECT contexts.context_key FROM contexts JOIN articles ON (articles.context_key = contexts.context_key) JOIN matview_82034 ON (matview_82034.context_key = contexts.context_key) WHERE EXISTS ( SELECT * FROM article_words JOIN words using (word_key) WHERE context_key = contexts.context_key AND word = 'insider' ) AND EXISTS ( SELECT * FROM article_words JOIN words using (word_key) WHERE context_key = contexts.context_key AND word = 'trading' ) AND EXISTS ( SELECT * FROM virtual_ancestors a JOIN bp_categories ON (bp_categories.context_key = a.ancestor_key) WHERE a.context_key = contexts.context_key AND lower(bp_categories.category) = 'law' ) AND articles.indexed ; 512,600ms query becomes 225,976ms. Twice as fast on pos Definitely not beating the 7500ms version. PostgreSQL 8.3.4 EXPLAIN ANALYZE SELECT contexts.context_key FROM contexts JOIN articles ON (articles.context_key = contexts.context_key) JOIN matview_82034 ON (matview_82034.context_key = contexts.context_key) WHERE EXISTS ( SELECT * FROM article_words JOIN words using (word_key) WHERE context_key = contexts.context_key AND word = 'insider' ) AND EXISTS ( SELECT * FROM article_words JOIN words using (word_key) WHERE context_key = contexts.context_key AND word = 'trading' ) AND EXISTS ( SELECT * FROM virtual_ancestors a JOIN bp_categories ON (bp_categories.context_key = a.ancestor_key) WHERE a.context_key = contexts.context_key AND lower(bp_categories.category) = 'law' ) AND articles.indexed ; Nested Loop (cost=13511.95..13662023.05 rows=19282 width=4) (actual time=3070.834..225973.159 rows=622 loops=1) - Hash Join (cost=13511.95..356938.38 rows=177937 width=8) (actual time=1106.242..7520.756 rows=351337 loops=1) Hash Cond: (articles.context_key = matview_82034.context_key) - Seq Scan on articles (cost=0.00..334502.90 rows=386266 width=4) (actual time=0.030..4031.203 rows=355626 loops=1) Filter: indexed - Hash (cost=6322.20..6322.20 rows=438220 width=4) (actual time=1105.663..1105.663 rows=438220 loops=1) - Seq Scan on matview_82034 (cost=0.00..6322.20 rows=438220 width=4) (actual time=7.105..544.072 rows=438220 loops=1) - Index Scan using contexts_pkey on contexts (cost=0.00..74.76 rows=1 width=4) (actual time=0.619..0.619 rows=0 loops=351337) Index Cond: (contexts.context_key = articles.context_key) Filter: ((subplan) AND (subplan) AND (subplan)) SubPlan - Nested Loop (cost=0.00..30.54 rows=1 width=17) (actual time=6.119..6.119 rows=1 loops=983) - Index Scan using words_word on words (cost=0.00..5.50 rows=1 width=13) (actual time=0.028..0.029 rows=1 loops=983) Index Cond: ((word)::text = 'trading'::text) - Index Scan using article_words_cw on article_words (cost=0.00..25.02 rows=1 width=8) (actual time=6.082..6.082 rows=1 loops=983) Index Cond: ((public.article_words.context_key = $0) AND (public.article_words.word_key = public.words.word_key)) - Nested Loop (cost=0.00..30.54 rows=1 width=17) (actual time=6.196..6.196 rows=0 loops=26494) - Index Scan using words_word on words (cost=0.00..5.50 rows=1 width=13) (actual time=0.022..0.024 rows=1 loops=26494) Index Cond: ((word)::text = 'insider'::text) - Index Scan using article_words_cw on article_words (cost=0.00..25.02 rows=1 width=8) (actual time=6.165..6.165 rows=0 loops=26494) Index Cond: ((public.article_words.context_key = $0) AND (public.article_words.word_key = public.words.word_key)) - Nested Loop (cost=0.00..38.38 rows=3 width=29) (actual time=0.122..0.122 rows=0 loops=351337) - Index Scan using virtual_context_key_idx on virtual_ancestors a (cost=0.00..7.35 rows=5 width=10) (actual time=0.074..0.085 rows=5
Re: [PERFORM] Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Tom Lane wrote: Given that it estimated 1 row out of words (quite correctly) and 12264 rows out of each scan on article_words, you'd think that the join size estimate would be 12264, which would be off by only a factor of 3 from the true result. Instead it's 23, off by a factor of 200 :-(. Has anyone every proposed a learning query planner? One that eventually clues in to estimate mismatches like this? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
So as the op, back to the original posting In the real world, what should I do? Does it make sense to pull the "AND articles.indexed" clause into an outer query? Will that query simply perform poorly on other arbitrary combinations of words? I'm happy to test any given query against the same set of servers. If it involves a persistent change it has to run on a test server). For example, the Robert Haas method: # ... Total runtime: 254207.857 ms # ALTER TABLE article_words ALTER COLUMN word_key SET STATISTICS 1000; # ANALYZE VERBOSE article_words INFO: analyzing "public.article_words" INFO: "article_words": scanned 30 of 1342374 pages, containing 64534899 live rows and 3264839 dead rows; 30 rows in sample, 288766568 estimated total rows ANALYZE # ... Total runtime: 200591.751 ms # ALTER TABLE article_words ALTER COLUMN word_key SET STATISTICS 50; # ANALYZE VERBOSE article_words # ... Total runtime: 201204.972 ms Sadly, it made essentially zero difference. Attached. preproduction-20091214=# EXPLAIN ANALYZE SELECT contexts.context_key FROM contexts JOIN articles ON (articles.context_key=contexts.context_key) JOIN matview_82034 ON (contexts.context_key=matview_82034.context_key) WHERE contexts.context_key IN (SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'insider' INTERSECT SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'trading') AND contexts.context_key IN (SELECT a.context_key FROM virtual_ancestors a JOIN bp_categories ON (a.ancestor_key = bp_categories.context_key) WHERE lower(bp_categories.category) = 'law') AND articles.indexed; QUERY PLAN --- Nested Loop (cost=12861.91..62150.21 rows=2 width=4) (actual time=136.789..200744.455 rows=546 loops=1) - Nested Loop IN Join (cost=12861.91..62148.32 rows=1 width=16) (actual time=136.777..200737.004 rows=546 loops=1) Join Filter: (a.context_key = articles.context_key) - Nested Loop (cost=12861.91..12875.48 rows=2 width=12) (actual time=55.674..116.443 rows=1306 loops=1) - Nested Loop (cost=12861.91..12871.68 rows=2 width=8) (actual time=55.662..97.863 rows=1306 loops=1) - Subquery Scan IN_subquery (cost=12861.91..12862.18 rows=5 width=4) (actual time=55.639..75.777 rows=1473 loops=1) - SetOp Intersect (cost=12861.91..12862.14 rows=5 width=4) (actual time=55.638..73.724 rows=1473 loops=1) - Sort (cost=12861.91..12862.02 rows=46 width=4) (actual time=55.631..62.140 rows=17892 loops=1) Sort Key: *SELECT* 1.context_key Sort Method: quicksort Memory: 1607kB - Append (cost=0.00..12860.63 rows=46 width=4) (actual time=0.040..42.026 rows=17892 loops=1) - Subquery Scan *SELECT* 1 (cost=0.00..6430.32 rows=23 width=4) (actual time=0.039..6.909 rows=3583 loops=1) - Nested Loop (cost=0.00..6430.09 rows=23 width=4) (actual time=0.038..5.110 rows=3583 loops=1) - Index Scan using words_word on words (cost=0.00..2.22 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1) Index Cond: ((word)::text = 'insider'::text) - Index Scan using article_words_wc on article_words (cost=0.00..6323.81 rows=8325 width=8) (actual time=0.015..3.243 rows=3583 loops=1) Index Cond: (public.article_words.word_key = public.words.word_key) - Subquery Scan *SELECT* 2 (cost=0.00..6430.32 rows=23 width=4) (actual time=0.036..27.166 rows=14309 loops=1) - Nested Loop (cost=0.00..6430.09 rows=23 width=4) (actual time=0.035..20.037 rows=14309 loops=1) - Index Scan using words_word on words (cost=0.00..2.22 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1) Index Cond: ((word)::text = 'trading'::text) - Index Scan using article_words_wc on article_words (cost=0.00..6323.81 rows=8325 width=8) (actual time=0.017..12.618 rows=14309
