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

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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com