Re: Row estimates off by two orders of magnitude with hstore

On Wed, Jun 10, 2015 at 2:08 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Wed, Jun 10, 2015 at 3:55 PM, Patrick Krecker <patrick(at)judicata(dot)com> wrote:
>> OK. Well, fortunately for us, we have a lot of possible solutions this
>> problem, and it sounds like actually getting statistics for attributes
>> ? 'reference' is not realistic. I just wanted to make sure it wasn't
>> some configuration error on our part.
>>
>> Can anyone explain where exactly the estimate for that clause comes from?
>>
>> I tried adding an index and I don't think it improved the estimation,
>> the planner still thinks there will be 9k rows as a result of type_id
>> = 23 and attributes ? 'reference'. [1]. It might make the pathological
>> plan less likely though. It's not clear to me that it reduces the risk
>> of a pathological plan to zero.
>
> no, but done in conjunction with disabling managing out nestloops and
> materliaze query plans, nestloops (say, via SET LOCAL) it will
> probably be fast and future proof..
>
> merlin

Wouldn't wrapping it in an optimization fence (e.g. SELECT * FROM
(...) AS t WHERE t.attributes ? 'reference') have the same effect as
disabling materialize, but allow the planner to optimize the inner
query however it wants?