Re: Values larger than 1/3 of a buffer page cannot be indexed (hstore)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stefan Keller <sfkeller(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>, oleg(at)sai(dot)msu(dot)su, Ott Michel <m1ott(at)hsr(dot)ch>
Subject: Re: Values larger than 1/3 of a buffer page cannot be indexed (hstore)
Date: 2011-05-01 16:41:46
Message-ID: 19054.1304268106@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Stefan Keller <sfkeller(at)gmail(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Iztok Stotl 2011-05-01 19:32:22 Help with database recovery ...
Previous Message Tom Lane 2011-05-01 16:35:09 Re: Postgresql, PSN hack and table limits