Re: [PERFORM] Poor plan when joining against a union containing a join
On 7 March 2013 05:52, Tom Lane wrote: > Josh Berkus writes: >> On 03/06/2013 06:54 AM, David Leverton wrote: >>> I'm encountering very poor query plans when joining against a union, > >> Actually, in case #4, Postgres *is* pushing down the join qual into the >> segments of the Union. > > Yeah, but not further. I believe the core issue here (as of 9.2) is > that we're not willing to generate parameterized paths for subquery > relations. We could do that without a huge amount of new code, > I think, but the scary thing is how much time it might take to generate > (and then discard most of the) plans for assorted parameterizations of > complicated subqueries. Thanks for looking at this, both of you. Does "as of 9.2" mean it's better in 9.3? I do intend to upgrade once it's released, so if it can handle this better (or if there's anything that can be done to improve it between now and then without making other things worse) that would be great. Otherwise, I'm wondering if the addition of LATERAL will help persuade the planner to do what I want, something like this, perhaps? (please excuse any syntax misunderstandings): SELECT * FROM item_reference, LATERAL ( SELECT * FROM item WHERE (item.item_id_a, item.item_id_b) = (item_reference.item_id_a, item_reference.item_id_b) ) item WHERE reference_id = 1; I'm hoping this might help as the query in the test case where the desired item_id_a and item_id_b were supplied literally rather than from a join was fast, and this version has a similar structure, although naturally it'll only work if the planner doesn't notice that it's really equivalent to the slow version and treat it the same way. If not though, and in the meantime in any case, I suppose I'm looking for a workaround. In the real application the queries involved are generated by code rather than hand-written, so it's not a disaster if they have to be uglified a bit more than they are already. I'll see if I can figure something out, but if anyone has any suggestions they would be much appreciated. I'm afraid I don't really see how Josh's outer join suggestion would help here, though, unless it was more of a general principle than something specific to this case. The two branches of the union don't have any tables in common, so I don't see what I could be joining to. Ideally any alternative would keep the semantics the same as the existing version, or at least as similar as possible, as the application does need (or at least very much wants) to be able to work with items, including using them in further joins, without caring whether they're loose or part of a bundle. (And yes, it is a rather scary design in places, but it's the best thing I could come up with to achieve the requirements. Not sure if that says more about the requirements or me) -- 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] Poor plan when joining against a union containing a join
David Leverton writes: > On 7 March 2013 05:52, Tom Lane wrote: >> Josh Berkus writes: >>> Actually, in case #4, Postgres *is* pushing down the join qual into the >>> segments of the Union. >> Yeah, but not further. I believe the core issue here (as of 9.2) is >> that we're not willing to generate parameterized paths for subquery >> relations. We could do that without a huge amount of new code, >> I think, but the scary thing is how much time it might take to generate >> (and then discard most of the) plans for assorted parameterizations of >> complicated subqueries. > Thanks for looking at this, both of you. > Does "as of 9.2" mean it's better in 9.3? No, I meant it was worse before 9.2 --- previous versions weren't even theoretically capable of generating the plan shape you want. What you're after is for the sub-join to be treated as a parameterized sub-plan, and we did not have any ability to do that for anything more complicated than a single-relation scan. > I do intend to upgrade once > it's released, so if it can handle this better (or if there's anything > that can be done to improve it between now and then without making > other things worse) that would be great. Otherwise, I'm wondering if > the addition of LATERAL will help persuade the planner to do what I > want, something like this, perhaps? Good idea, but no such luck in that form: it's still not going to try to push the parameterization down into the sub-query. I think you'd have to write out the query with the views expanded and manually put the WHERE restrictions into the lowest join level. [ experiments... ] Looks like only the UNION view has to be manually expanded to get a good plan with HEAD: regression=# explain SELECT * FROM item_reference, LATERAL ( SELECT item_id_a, item_id_b FROM bundled_item WHERE (item_id_a, item_id_b) = (item_reference.item_id_a, item_reference.item_id_b) UNION ALL SELECT item_id_a, item_id_b FROM unbundled_item WHERE (item_id_a, item_id_b) = (item_reference.item_id_a, item_reference.item_id_b) ) item WHERE reference_id = 1; QUERY PLAN --- Nested Loop (cost=0.57..25.99 rows=2 width=20) -> Seq Scan on item_reference (cost=0.00..1.02 rows=1 width=12) Filter: (reference_id = 1) -> Append (cost=0.57..24.94 rows=2 width=8) -> Nested Loop (cost=0.57..16.61 rows=1 width=8) Join Filter: (bundle.bundle_type = bundle_contents.bundle_type) -> Index Scan using bundle_pkey on bundle (cost=0.29..8.31 rows=1 width=8) Index Cond: (bundle_id = item_reference.item_id_a) -> Index Scan using bundle_contents_pkey on bundle_contents (cost=0.28..8.29 rows=1 width=8) Index Cond: (item_id = item_reference.item_id_b) -> Index Only Scan using unbundled_item_pkey on unbundled_item (cost=0.29..8.31 rows=1 width=8) Index Cond: ((item_id_a = item_reference.item_id_a) AND (item_id_b = item_reference.item_id_b)) (12 rows) You might be able to accomplish something similar without LATERAL, if you're willing to give up the notational convenience of the views. Don't have time right now to experiment further 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
[PERFORM] Anyone running Intel S3700 SSDs?
Considering this list is where I first learned of the Intel 320 drives (AFAIK, the only non-enterprise SSDs that are power-failure safe), I thought I'd see if any of the folks here that tend to test new stuff have got their hands on these yet. I had no idea these drives were out (but they still are a bit pricey, but cheaper than any spinning drives that would give the same sort of random IO performance), and while trying to find a place to source some spare 300GB 320s, I found this review: http://www.anandtech.com/show/6433/intel-ssd-dc-s3700-200gb-review Of most interest to me was this: "Along one edge of the drive Intel uses two 35V 47µF capacitors, enough to allow the controller to commit any data (and most non-data) to NAND in the event of a power failure. The capacitors in the S3700 are periodically tested by the controller. In the event that they fail, the controller disables all write buffering and throws a SMART error flag." This is also the first new Intel drive in a long time to use an Intel controller rather than a SandForce (which frankly, I don't trust). Anyone have any benchmarks to share? Are there any other sub-$1K drives out there currently that incorporate power loss protection like this and the 320s do? Thanks, Charles -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
