Skip site navigation (1) Skip section navigation (2)

Accounting for toast in query planner. (gin/gist indexes).

From: Jesper Krogh <jesper(at)krogh(dot)cc>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Accounting for toast in query planner. (gin/gist indexes).
Date: 2011-11-30 20:15:40
Message-ID: 4ED68EEC.9030906@krogh.cc (view raw or flat)
Thread:
Lists: 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.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

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-11-30 20:26:54
Subject: Re: FlexLocks
Previous:From: Joel JacobsonDate: 2011-11-30 20:13:25
Subject: Re: Java LISTEN/NOTIFY client library work-around

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group