Re: Problems with FTS

On Tue, Jan 11, 2011 at 3:16 AM, Rauan Maemirov <rauan(at)maemirov(dot)com> wrote:
> Hi, Kevin.
> Sorry for long delay.
> EXPLAIN ANALYZE SELECT "v"."id", "v"."title" FROM "video" AS "v"
> WHERE (v.active) AND (v.fts @@
> 'dexter:A|season:A|seri:A|декстер:A|качество:A|сезон:A|серия:A'::tsquery and
> v.id <> 500563 )
> ORDER BY COALESCE(ts_rank_cd( '{0.1, 0.2, 0.7, 1.0}', v.fts,
> 'dexter:A|season:A|seri:A|декстер:A|качество:A|сезон:A|серия:A'::tsquery),
> 1) DESC, v.views DESC
> LIMIT 6
> "Limit  (cost=103975.50..103975.52 rows=6 width=280) (actual
> time=2893.193..2893.199 rows=6 loops=1)"
> "  ->  Sort  (cost=103975.50..104206.07 rows=92228 width=280) (actual
> time=2893.189..2893.193 rows=6 loops=1)"
> "        Sort Key: (COALESCE(ts_rank_cd('{0.1,0.2,0.7,1}'::real[], fts, '( (
> ( ( ( ''dexter'':A | ''season'':A ) | ''seri'':A ) | ''декстер'':A ) |
> ''качество'':A ) | ''сезон'':A ) | ''серия'':A'::tsquery), 1::real)), views"
> "        Sort Method:  top-N heapsort  Memory: 25kB"
> "        ->  Seq Scan on video v  (cost=0.00..102322.34 rows=92228
> width=280) (actual time=0.100..2846.639 rows=54509 loops=1)"
> "              Filter: (active AND (fts @@ '( ( ( ( ( ''dexter'':A |
> ''season'':A ) | ''seri'':A ) | ''декстер'':A ) | ''качество'':A ) |
> ''сезон'':A ) | ''серия'':A'::tsquery) AND (id <> 500563))"
> "Total runtime: 2893.264 ms"
> Table scheme:
> CREATE TABLE video
> (
>   id bigserial NOT NULL,
>   hash character varying(12),
>   account_id bigint NOT NULL,
>   category_id smallint NOT NULL,
>   converted boolean NOT NULL DEFAULT false,
>   active boolean NOT NULL DEFAULT true,
>   title character varying(255),
>   description text,
>   tags character varying(1000),
>   authorized boolean NOT NULL DEFAULT false,
>   adult boolean NOT NULL DEFAULT false,
>   views bigint DEFAULT 0,
>   rating real NOT NULL DEFAULT 0,
>   screen smallint DEFAULT 2,
>   duration smallint,
>   "type" smallint DEFAULT 0,
>   mp4 smallint NOT NULL DEFAULT 0,
>   size bigint,
>   size_high bigint DEFAULT 0,
>   source character varying(255),
>   storage_id smallint NOT NULL DEFAULT 1,
>   rule_watching smallint,
>   rule_commenting smallint,
>   count_comments integer NOT NULL DEFAULT 0,
>   count_likes integer NOT NULL DEFAULT 0,
>   count_faves integer NOT NULL DEFAULT 0,
>   fts tsvector,
>   modified timestamp without time zone NOT NULL DEFAULT now(),
>   created timestamp without time zone DEFAULT now(),
>   CONSTRAINT video_pkey PRIMARY KEY (id),
>   CONSTRAINT video_hash_key UNIQUE (hash)
> )
> WITH (
>   OIDS=FALSE
> );
> Indexes:
> CREATE INDEX idx_video_account_id  ON video  USING btree  (account_id);
> CREATE INDEX idx_video_created  ON video  USING btree  (created);
> CREATE INDEX idx_video_fts  ON video  USING gin  (fts);
> CREATE INDEX idx_video_hash  ON video  USING hash  (hash);
> (here I tried both gist and gin indexes)
> I have 32Gb ram and 2 core quad E5520, 2.27GHz (8Mb cache).
> Pgsql conf:
> max_connections = 200
> shared_buffers = 7680MB
> work_mem = 128MB
> maintenance_work_mem = 1GB
> effective_cache_size = 22GB
> default_statistics_target = 100
> Anything else?

For returning that many rows, an index scan might actually be slower.
Maybe it's worth testing. Try:

SET enable_seqscan=off;
EXPLAIN ANALYZE ...

and see what you get. If it's slower, well, then be happy it didn't
use the index (maybe the question is... what index should you have
instead?). If it's faster, post the results...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company