Re: Planner reluctant to start from subquery

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>>> On Wed, Feb 1, 2006 at  2:36 pm, in message
<4359.1138826175@xxxxxxxxxxxxx>,
Tom Lane <tgl@xxxxxxxxxxxxx> wrote: 
> "Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes:
>> Tom Lane <tgl@xxxxxxxxxxxxx> wrote: 
>>> I'm interested to poke at this ... are you in a position to provide
a
>>> test case?
> 
>> I can't supply the original data, since many of the tables have
>> millions of rows, with some of the data (related to juvenile,
paternity,
>> sealed, and expunged cases) protected by law.  I could try to put
>> together a self- contained example, but I'm not sure the best way to
do
>> that, since the table sizes and value distributions may be
significant
>> here.  Any thoughts on that?
> 
> I think that the only aspect of the data that really matters here is
the
> number of distinct values, which would affect decisions about
whether
> HashAggregate is appropriate or not.  And you could probably get the
> same thing to happen with at most a few tens of thousands of rows.
> 
> Also, all we need to worry about is the columns used in the
WHERE/JOIN
> conditions, which looks to be mostly case numbers, dates, and county
> identification ... how much confidential info is there in that?  At
> worst you could translate the case numbers to some randomly
generated
> identifiers.

OK, I could probably obliterate name, addresses, etc. in a copy of the
data (those aren't significant to the query anyway) and provide a test
case.  However, I just found another clue.

Since you were so confident it couldn't be the outer join, I went
looking for what else I changed at the same time.  I eliminated the code
referencing that table, which contained an OR.  I've seen ORs cause
nasty problems with optimizers in the past.  I took out the OR in the
where clause, without eliminating that last outer join, and it optimized
fine.

I'll hold off a bit to see if you still need the test case.   ;-)

-Kevin



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux