On Tue, Jan 11, 2011 at 3:16 AM, Rauan Maemirov <rauan@xxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
