Accounting for toast in query planner. (gin/gist indexes). - Mailing list pgsql-hackers

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.50rows=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.15rows=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