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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
