hstore query: Any better idea than adding more memory?

Поиск
Список
Период
Сортировка
От Stefan Keller
Тема hstore query: Any better idea than adding more memory?
Дата
Msg-id CAFcOn293DCQd-OsoPaTu8Upo2PJw0X2XmCJoe+BWjBGSm0eqbg@mail.gmail.com
обсуждение исходный текст
Ответы Re: hstore query: Any better idea than adding more memory?
Список pgsql-performance
Hi all

I'd like to tune the following hstore-related query which selects all
Zoos from table osm_point:

SELECT osm_id, name, tags
  FROM osm_point
  WHERE tags @> hstore('tourism','zoo')
  ORDER BY name;

... given the following table and indexes definition:

CREATE TABLE osm_point (
  osm_id integer,
  name text,
  tags hstore,
  way geometry
)

CREATE INDEX osm_point_index ON osm_point USING gist (way);
CREATE INDEX osm_point_name_idx ON osm_point USING btree  (name) WITH
(FILLFACTOR=100);
ALTER TABLE osm_point CLUSTER ON osm_point_name_idx;
CREATE INDEX osm_point_pkey ON osm_point USING btree (osm_id);
CREATE INDEX osm_point_tags_idx ON osm_point USING gist (tags) WITH
(FILLFACTOR=100);

... and following statistics:
* Live Tuples              9626138  (that's also what COUNT(*) returns)
* Table Size                    1029 MB
* Toast Table Size              32 kB
* Indexes Size                1381 MB (?)
** osm_point_index         1029 MB
** osm_point_name_idx   1029 MB
** osm_point_pkey          1029 MB
** osm_point_tags_idx     1029 MB

PostgreSQL has version 9.0.4, runs on on Ubuntu Linux 10.04 LTS
(64-Bit) with 1 vCPU and 1 GB vRAM.
Adding more memory (say to total of 32 GB) would only postpone the problem.
I already increased the PostgreSQL configuration of shared_buffers
(using pgtune).

Now EXPLAIN ANALYZE returns (if run several times):
Sort  (cost=30819.51..30843.58 rows=9626 width=65) (actual
time=11.502..11.502 rows=19 loops=1)
  Sort Key: name
  Sort Method:  quicksort  Memory: 29kB
  ->  Bitmap Heap Scan on osm_point  (cost=313.21..30182.62 rows=9626
width=65) (actual time=10.727..11.473 rows=19 loops=1)
        Recheck Cond: (tags @> 'tourism=>zoo'::hstore)
        ->  Bitmap Index Scan on osm_point_tags_idx
(cost=0.00..310.80 rows=9626 width=0) (actual time=10.399..10.399
rows=591 loops=1)
              Index Cond: (tags @> 'tourism=>zoo'::hstore)
Total runtime: 11 ms

First time the query lasts about 10 time longer (~ 1010 ms) - but I'd
like to get better results already in the first query.

=> 1. When I add the "actual time" from EXPLAIN above, I get 11 + 10 +
10ms which is three times greater than the 11ms reported.  Why?
=> 2. Why does the planner choose to sort first instead of sorting the
(smaller)  result query at the end the?
=> 3. What could I do to speed up such queries (first time, i.e.
without caching) besides simply adding more memory?

Yours, Stefan

В списке pgsql-performance по дате отправления:

Предыдущее
От: Anthony Presley
Дата:
Сообщение: Re: 8.4.4, 9.0, and 9.1 Planner Differences
Следующее
От: Tom Lane
Дата:
Сообщение: Re: 8.4.4, 9.0, and 9.1 Planner Differences