[OSM-Devserver] Partitionierung

[Peter Körner]

Sven Geggus schrieb:
> Sven Geggus <lists at fuchsschwanzdomain.de> wrote:
> 
>> Aber sowas (untested) könnte klappen:
>>
>> CREATE INDEX planet_osm_hstore_point_btidx ON planet_osm_hstore_point
>> USING BTREE (tags) where (tags ? 'amenity');
> 
> Erzeugen kann man den Index:
> 
> hstore-rendering=> \d planet_osm_hstore_point
> Tabelle »public.planet_osm_hstore_point«
>  Spalte |   Typ    | Attribute 
> --------+----------+-----------
>  osm_id | integer  | 
>  tags   | hstore   | 
>  way    | geometry | 
> Indexe:
>     »planet_osm_hstore_point_btidx« btree (tags) WHERE tags ? 'amenity'::text
>     »planet_osm_hstore_point_index« gist (way)
>     »planet_osm_hstore_point_pkey« btree (osm_id)
>     »planet_osm_hstore_point_tags« gin (tags)
> 
> Keine Ahnung ob er hilft.

Benutzt wird er bei normalen queries nicht:

hstore-rendering=> explain select osm_id from planet_osm_hstore_point 
where (tags ? 'amenity');
                                             QUERY PLAN
--------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on planet_osm_hstore_point  (cost=1208.11..63501.88 
rows=18234 width=4)
    Recheck Cond: (tags ? 'amenity'::text)
    ->  Bitmap Index Scan on planet_osm_hstore_point_tags 
(cost=0.00..1203.55 rows=18234 width=0)
          Index Cond: (tags ? 'amenity'::text)
(4 Zeilen)

hstore-rendering=>  explain select osm_id from planet_osm_hstore_point 
where (tags->'amenity' = 'restaurant');
                                    QUERY PLAN
--------------------------------------------------------------------------------
  Seq Scan on planet_osm_hstore_point  (cost=0.00..826824.89 rows=91169 
width=4)
    Filter: ((tags -> 'amenity'::text) = 'restaurant'::text)
(2 Zeilen)

hstore-rendering=> set enable_seqscan = off;
SET
hstore-rendering=> explain select osm_id from planet_osm_hstore_point 
where (tags->'amenity' = 'restaurant');
                                         QUERY PLAN
-------------------------------------------------------------------------------------------
  Seq Scan on planet_osm_hstore_point  (cost=100000000.00..100826824.89 
rows=91169 width=4)
    Filter: ((tags -> 'amenity'::text) = 'restaurant'::text)
(2 Zeilen)

hstore-rendering=> set enable_seqscan = on;
SET
hstore-rendering=> explain select osm_id from planet_osm_hstore_point 
where (tags ? 'amenity') and (tags->'amenity' = 'restaurant');
                                             QUERY PLAN
--------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on planet_osm_hstore_point  (cost=1203.57..63588.51 
rows=91 width=4)
    Recheck Cond: (tags ? 'amenity'::text)
    Filter: ((tags -> 'amenity'::text) = 'restaurant'::text)
    ->  Bitmap Index Scan on planet_osm_hstore_point_tags 
(cost=0.00..1203.55 rows=18234 width=0)
          Index Cond: (tags ? 'amenity'::text)
(5 Zeilen)