hstore query: Any better idea than adding more memory?

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: hstore query: Any better idea than adding more memory?
Date: 2011-10-22 23:33:53
Message-ID: CAFcOn293DCQd-OsoPaTu8Upo2PJw0X2XmCJoe+BWjBGSm0eqbg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-10-23 02:31:59 Re: 8.4.4, 9.0, and 9.1 Planner Differences
Previous Message Anthony Presley 2011-10-22 23:12:51 Re: 8.4.4, 9.0, and 9.1 Planner Differences