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?
2010/12/18 Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx>
Rauan Maemirov Âwrote:
> EXPLAIN SELECT [...]
Please show us the results of EXPLAIN ANALYZE SELECT ...
Also, please show us the table layout (including indexes), and
details about your hardware and PostgreSQL configuration. ÂSee this
page for details:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
That means that either the optimizer thinks that the index isn't
> As you can see the query doesn't use index.
usable for this query (due to type mismatch or some such) or that it
thinks a plan without the index costs less to run (i.e., it will
generally run faster). ÂYou haven't told us enough to know whether
that is actually true, much less how to allow PostgreSQL to develop
more accurate costing estimates in your environment if it's currently
wrong about this.
-Kevin
