Re: JOIN performance
От | Dean Gibson (DB Administrator) |
---|---|
Тема | Re: JOIN performance |
Дата | |
Msg-id | 5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net обсуждение исходный текст |
Ответ на | Re: JOIN performance (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: JOIN performance
(Chester Kustarz <chester@arbor.net>)
Re: JOIN performance (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-sql |
Ahh, that explains why a "plain" JOIN (which doesn't do what I need) gave much better performance than the LEFT JOIN. I could ask why a CASE statement is always non-nullable, but I don't think the answer would help be solve my problem. <grin> I suppose it's that even though my particular CASE statement has WHEN/ELSE values that come from the nullable side of the JOIN, in general that's not true ... Okay, now for my big question: I searched high and low for a function that would return the minimum of two dates, and found none. Now you come up with "date_smaller", which works fine (as does "date_larger"), but where are those documented? More importantly, where are other functions like them documented? -- Dean ps: Who dreamt up the names "date_smaller" and "date_larger" ??? Much more intuitive are "min_date" and "max_date". pps: I realize that "date_smaller" isn't exactly equivalent to my CASE statement; a NULL value for one of the CASE operands causes the result of the ELSE clause to be returned, whereas "date_smaller" just returns NULL in that case. In my data, that's significant. I suppose that COALESCE has the same problem as CASE ... Tom Lane wrote on 2004-09-20 17:54: >"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes: >Question: Why do the last two column definitions in the second VIEW >change the scan on _LicHD from indexed to sequential ?? > >It's the CASE that's getting you. The poor plan is basically because the >sub-view isn't getting "flattened" into the upper query, and so it's not >possible to choose a plan for it that's dependent on the upper query >context. And the reason it's not getting flattened is that subselects >that are on the nullable side of an outer join can't be flattened unless >they have nullable targetlists --- otherwise the results might not go to >NULL when they are supposed to. A CASE construct is always going to be >treated as non-nullable. > >Fixing this properly is a research project, and I haven't thought of any >quick-and-dirty hacks that aren't too ugly to consider :-( > >In the meantime, you could easily replace that CASE construct with a min() >function that's declared strict. I think date_smaller would do nicely, >assuming the columns are actually of type date.
В списке pgsql-sql по дате отправления: