Re: Values larger than 1/3 of a buffer page cannot be indexed (hstore) - Mailing list pgsql-general

Stefan Keller <sfkeller@gmail.com> writes:
> I'm doing an equality search with success with the '->' operator on
> the same field 'tags' like in this query (1):

> -- Count all restaurants in database ("amenity = restaurant''):
> select count(*) from osm_all_v
> where hstore(tags)->'amenity'='restaurant'

> This query 1 is reasonably fast and could be accelerated using this
> functional index:
> CREATE INDEX planet_osm_point_tags_restaurant
>   ON planet_osm_point
>   USING btree (tags)
>   WHERE (tags -> 'amenity'::text) = 'restaurant'::text;

This index seems a bit carelessly defined.  There's no need to confine
its usefulness to exactly that query, and there's no point in having the
index column contents be the entire tags value (which is what's leading
to the failure).  Consider

create index planet_osm_point_amenity on planet_osm_point ((tags->amenity));

which will work for the above query and any other that's looking for a
specific value of tags->amenity.

> SELECT MIN(keys.key), hstore(p.tags)->keys.key, count(*)
> FROM planet_osm_point p, (
>   SELECT key, count(*)
>   FROM (
>     SELECT (each(tags)).key FROM planet_osm_point
>   ) AS stat
> GROUP BY key HAVING count(*) > 1
>   AND key NOT LIKE 'note:%'
>   ...
>   AND key NOT IN
> ('ele','ref','url','website','email','maxspeed','converted_by', ... )
> ) keys
> WHERE hstore(p.tags)->keys.key >''
> GROUP BY hstore(p.tags)->keys.key HAVING count(*) > 1
> ORDER by 1,3 desc

It's pretty much useless to think about indexes for queries like this.
If it's going to scan the whole table anyway, as this surely is, then
an index is not going to make it faster.

            regards, tom lane