BUG #8048: Text Search
От | luigisag@gmail.com |
---|---|
Тема | BUG #8048: Text Search |
Дата | |
Msg-id | E1UPV93-0002Dp-Lc@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #8048: Text Search
Re: BUG #8048: Text Search |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 8048 Logged by: Luigi Email address: luigisag@gmail.com PostgreSQL version: 9.2.0 Operating system: Windows 7 Description: = I've configured 2 table like this CREATE TABLE "User_Full_Text_Search" ( "Email" varchar(50), "UserId" varchar(50), "Full_Text_Search" varchar(4096) ) WITH (OIDS=3DFALSE) ; = ALTER TABLE "User_Full_Text_Search" OWNER TO "postgres"; = CREATE INDEX IX_FullText ON "User_Full_Text_Search" USING gin(to_tsvector('italian', "Full_Text_Search")); = = CREATE TABLE "User_Full_Text_Search_2" ( "Email" varchar(50), "UserId" varchar(50), "Full_Text_Search" varchar(4096), "tsv" varchar(4096) ) WITH (OIDS=3DFALSE) ; = ALTER TABLE "User_Full_Text_Search_2" OWNER TO "postgres"; = CREATE INDEX IX_FullText_2 ON "User_Full_Text_Search_2" USING gin("tsv"); CREATE TRIGGER "tsvectorupdate" BEFORE INSERT OR UPDATE ON "User_Full_Text_Search_2" FOR EACH ROW EXECUTE PROCEDURE "tsvector_update_trigger"('tsv', 'pg_catalog.italian', 'Full_Text_Search'); Column Full_Text_Search (table User_Full_Text_Search) is just a single word or max 2 words separeted by space " " (ex: test tester), and tsv (table User_Full_Text_Search_2) is populate by materializing column with a ts_vector of Full_Text_Search. Now if i perform those 2 queries select "UserId","Email" from "User_Full_Text_Search" = where to_tsvector('italian',"Full_Text_Search") @@ to_tsquery('italian', 'test|developer') GROUP BY "UserId","Email" = select "UserId","Email" from "User_Full_Text_Search_2" = where "tsv" @@ to_tsquery('italian', 'test|developer') GROUP BY "UserId","Email" Records on Tables (are same) like 10 milion. = Execution time of 1st query is 120 seconds (result set like 750.000) Execution time of 2st query is 270 seconds (result set like 750.000) same records I don't understand why a materialized column is more slow than a calculeted one...
В списке pgsql-bugs по дате отправления: