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

On Wed, Jun 10, 2015 at 2:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 06/10/2015 11:32 AM, Merlin Moncure wrote:
>> This is a fundamental issue with using 'database in a box' datatypes
>> like hstore and jsonb. They are opaque to the statistics gathering
>> system and so are unable to give reasonable estimates beyond broad
>> assumptions. Speaking generally, the workarounds are too:
>>
>> *) disable particular plan choices for this query
>> (materialize/nestloop are common culprits)
>>
>> *) create btree indexes around specific extraction clauses
>>
>> *) refactor some of the query into set returning function with a
>> custom ROWS clause
>>
>> *) try alternate indexing strategy such as jsonb/jsquery
>>
>> *) move out of hstore and into more standard relational strucure
>
> You forgot:
>
> *) Fund a PostgreSQL developer to add selectivity estimation and stats
> to hstore.

Well, I don't know. That's really complex to the point of making me
wonder if it's worth doing even given infinite time and resources. If
it was my money, I'd be researching a clean way to inject estimate
returning expressions into the query that the planner could utilize.
Not 'hints' which are really about managing the output of the planner,
just what feeds in. Also lots of various solutions of alcohol to
lubricate the attendant -hackers discussions.

merlin