Hi list.
I have currently hit a problem which I dug into finding the cause for, in
particular, searching in GIN indices seems in some situations to
un-fairly favor Sequential Scans.
Googling a bit I found this page:
http://postgis.refractions.net/docs/ch06.html#id2635817
Describing the excact problem.
It seemed to be discussed back in the pre 8.1 days and wasn't
solved there, is there a chance someone may address it in 9.2 ?
http://archives.postgresql.org/pgsql-performance/2005-02/msg00041.php
Would you coin it a hard task or can a "fairly" naive C-coder, with
a fair amount of PG experience approach it?
Test-dataset can be created with:
CREATE table ftstest (id serial unique, fts tsvector);
DO
$$DECLARE r RECORD;
BEGIN
FOR r in SELECT generate_series(1,5000)
LOOP insert into ftstest(fts) (select
strip(to_tsvector('english',string_agg(test,' '))) from (select 'test'
|| generate_series(1,(select (random()*10000)::int)) as test ) as foo);
END LOOP;
END;
$$;
CREATE INDEX ON ftstest using gin(fts);
ANALYZE;
2011-11-30 21:13:30.302 jktest=# explain ( buffers on, analyze on )
select count(id) from ftstest where fts @@ to_tsquery('english','test500');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Aggregate (cost=122.37..122.38 rows=1 width=4) (actual
time=1114.096..1114.097 rows=1 loops=1)
Buffers: shared hit=13384 read=24445 written=3002
-> Seq Scan on ftstest (cost=0.00..110.50 rows=4748 width=4)
(actual time=0.567..1112.447 rows=4748 loops=1)
Filter: (fts @@ '''test500'''::tsquery)
Rows Removed by Filter: 252
Buffers: shared hit=13384 read=24445 written=3002
Total runtime: 1114.134 ms
(7 rows)
Time: 1114.945 ms
2011-11-30 21:14:30.382 jktest=# set enable_seqscan to off;
SET
Time: 0.132 ms
2011-11-30 21:14:50.965 jktest=# explain ( buffers on, analyze on )
select count(id) from ftstest where fts @@ to_tsquery('english','test500');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=184.02..184.03 rows=1 width=4) (actual
time=2.502..2.502 rows=1 loops=1)
Buffers: shared hit=1 read=56 written=3
-> Bitmap Heap Scan on ftstest (cost=64.80..172.15 rows=4748
width=4) (actual time=1.160..1.989 rows=4748 loops=1)
Recheck Cond: (fts @@ '''test500'''::tsquery)
Buffers: shared hit=1 read=56 written=3
-> Bitmap Index Scan on ftstest_fts_idx (cost=0.00..63.61
rows=4748 width=0) (actual time=1.137..1.137 rows=4748 loops=1)
Index Cond: (fts @@ '''test500'''::tsquery)
Buffers: shared hit=1 read=8
Total runtime: 2.529 ms
(9 rows)
Time: 3.016 ms
--
Jesper
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
