Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 9CA331FFFD22 for ; Tue, 3 Jul 2012 12:44:01 -0300 (ADT) Received: from mail-bk0-f46.google.com ([209.85.214.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Sm5Gf-0006J9-Uo for pgsql-performance@postgresql.org; Tue, 03 Jul 2012 15:44:01 +0000 Received: by mail-bk0-f46.google.com with SMTP id j10so1794729bkw.19 for ; Tue, 03 Jul 2012 08:43:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; bh=pssv6u5sbuyBsnOufU4aVVLbfb5Vncsh3Rf87cWmLr4=; b=T7VlATQFShM3cprOwk+FCdoWT4cPoWE31IDv8zaeTYxv3Z+Pfdzd2UoEP2WOQXR+ig g3W5v1n276yHh96k8BSNps6GftjfnWXXcCew6VuQwsQO6bTrvDuiEBXhkxr8O+0trYAc gbp4KcLe3u0xPn3neRHoFNAYlwnAw3Rf6pjYPuX/NTwC+h6efoc7fto9yA6vsnhEvaU8 KrYoog020GQCIzFWUBN0o752mNAtQTjvFO8ab7XCciARALZqLQz1w+Qdcf8msYaNXX+I tmqxXvchSvKcjQB0LWxM48t6jue6LoX9dJTqbtm/qRDE7U4XNMdAjH7/7urfJXGpG1p7 TtyA== Received: by 10.204.152.4 with SMTP id e4mr4318719bkw.2.1341330237281; Tue, 03 Jul 2012 08:43:57 -0700 (PDT) Received: from athlon.ww1.0pc.eu (66.Red-88-29-2.staticIP.rima-tde.net. [88.29.2.66]) by mx.google.com with ESMTPS id h18sm17606668bkh.8.2012.07.03.08.43.55 (version=SSLv3 cipher=OTHER); Tue, 03 Jul 2012 08:43:56 -0700 (PDT) Message-ID: <4FF3133A.6010801@gmail.com> Date: Tue, 03 Jul 2012 17:43:54 +0200 From: PV User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:12.0) Gecko/20120430 Thunderbird/12.0.1 MIME-Version: 1.0 To: Kevin Grittner CC: pgsql-performance@postgresql.org Subject: Re: static virtual columns as result? References: <4FF2B1020200002500048D3F@gw.wicourts.gov> In-Reply-To: <4FF2B1020200002500048D3F@gw.wicourts.gov> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201207/6 X-Sequence-Number: 47187 El 03/07/12 15:44, Kevin Grittner escribió: > You provided too little information to suggest much beyond using JOIN > instead of a subquery. Something like: I think that adding new columns to Product , lft and rgt with index should be fast. But does not seem a good design. Tables: ######################################### ######################################### -- Table: "Category" CREATE TABLE "Category" ( id serial NOT NULL, ... lft integer, rgt integer, ... path ltree, description text NOT NULL, idxfti tsvector, ... CONSTRAINT "Category_pkey" PRIMARY KEY (id ), ) WITH (OIDS=FALSE); ALTER TABLE "Category" OWNER TO root; CREATE INDEX "Category_idxfti_idx" ON "Category" USING gist (idxfti ); CREATE INDEX "Category_lftrgt_idx" ON "Category" USING btree (lft , rgt ); CREATE TRIGGER categorytsvectorupdate BEFORE INSERT OR UPDATE ON "Category" FOR EACH ROW EXECUTE PROCEDURE tsearch2('idxfti', 'description'); #################################### -- Table: "Product" CREATE TABLE "Product" ( id serial NOT NULL, ... description text NOT NULL, "Category" integer NOT NULL, ... creationtime integer NOT NULL, ... idxfti tsvector, ... CONSTRAINT product_pkey PRIMARY KEY (id ), CONSTRAINT product_creationtime_check CHECK (creationtime >= 0), ) WITH ( OIDS=FALSE ); CREATE INDEX "Product_Category_idx" ON "Product" USING btree ("Category" ); CREATE INDEX "Product_creationtime" ON "Product" USING btree (creationtime ); CREATE INDEX "Product_idxfti_idx" ON "Product" USING gist (idxfti ); CREATE TRIGGER producttsvectorupdate BEFORE INSERT OR UPDATE ON "Product" FOR EACH ROW EXECUTE PROCEDURE tsearch2('idxfti','description'); ################################# ######################################### Query ######################################### EXPLAIN ANALYZE SELECT * FROM "Product" AS p JOIN "Category" ON ("Category".id = p."Category") WHERE "lft" BETWEEN 1 AND 792 ORDER BY creationtime ASC OFFSET 0 LIMIT 40 "Limit (cost=2582.87..2582.97 rows=40 width=1688) (actual time=4306.209..4306.328 rows=40 loops=1)" " -> Sort (cost=2582.87..2584.40 rows=615 width=1688) (actual time=4306.205..4306.246 rows=40 loops=1)" " Sort Key: p.creationtime" " Sort Method: top-N heapsort Memory: 69kB" " -> Nested Loop (cost=31.21..2563.43 rows=615 width=1688) (actual time=0.256..3257.310 rows=122543 loops=1)" " -> Index Scan using "Category_lftrgt_idx" on "Category" (cost=0.00..12.29 rows=2 width=954) (actual time=0.102..18.598 rows=402 loops=1)" " Index Cond: ((lft >= 1) AND (lft <= 792))" " -> Bitmap Heap Scan on "Product" p (cost=31.21..1270.93 rows=371 width=734) (actual time=0.561..6.125 rows=305 loops=402)" " Recheck Cond: ("Category" = "Category".id)" " -> Bitmap Index Scan on "Product_Category_idx" (cost=0.00..31.12 rows=371 width=0) (actual time=0.350..0.350 rows=337 loops=402)" " Index Cond: ("Category" = "Category".id)" "Total runtime: 4306.706 ms" ######################################### EXPLAIN ANALYZE SELECT * FROM "Product" AS p WHERE (p."idxfti" @@ to_tsquery('simple', 'vpc')) ORDER BY creationtime ASC OFFSET 0 LIMIT 40 "Limit (cost=471.29..471.39 rows=40 width=734) (actual time=262.854..262.971 rows=40 loops=1)" " -> Sort (cost=471.29..471.57 rows=113 width=734) (actual time=262.850..262.890 rows=40 loops=1)" " Sort Key: creationtime" " Sort Method: top-N heapsort Memory: 68kB" " -> Bitmap Heap Scan on "Product" p (cost=49.62..467.72 rows=113 width=734) (actual time=258.502..262.322 rows=130 loops=1)" " Recheck Cond: (idxfti @@ '''vpc'''::tsquery)" " -> Bitmap Index Scan on "Product_idxfti_idx" (cost=0.00..49.60 rows=113 width=0) (actual time=258.340..258.340 rows=178 loops=1)" " Index Cond: (idxfti @@ '''vpc'''::tsquery)" "Total runtime: 263.177 ms" ######################################### And here is a big problem: EXPLAIN ANALYZE SELECT * FROM "Product" AS p JOIN "Category" ON ("Category".id = p."Category") WHERE "lft" BETWEEN 1 AND 792 AND (p."idxfti" @@ to_tsquery('simple', 'vpc')) ORDER BY creationtime ASC OFFSET 0 LIMIT 40 "Limit (cost=180.09..180.09 rows=1 width=1688) (actual time=26652.316..26652.424 rows=40 loops=1)" " -> Sort (cost=180.09..180.09 rows=1 width=1688) (actual time=26652.312..26652.350 rows=40 loops=1)" " Sort Key: p.creationtime" " Sort Method: top-N heapsort Memory: 96kB" " -> Nested Loop (cost=85.27..180.08 rows=1 width=1688) (actual time=12981.612..26651.594 rows=130 loops=1)" " -> Bitmap Heap Scan on "Category" (cost=4.27..10.03 rows=2 width=954) (actual time=0.215..1.580 rows=402 loops=1)" " Recheck Cond: ((lft >= 1) AND (lft <= 792))" " -> Bitmap Index Scan on "Category_lftrgt_idx" (cost=0.00..4.27 rows=2 width=0) (actual time=0.193..0.193 rows=402 loops=1)" " Index Cond: ((lft >= 1) AND (lft <= 792))" " -> Bitmap Heap Scan on "Product" p (cost=81.00..85.01 rows=1 width=734) (actual time=66.276..66.280 rows=0 loops=402)" " Recheck Cond: (("Category" = "Category".id) AND (idxfti @@ '''vpc'''::tsquery))" " -> BitmapAnd (cost=81.00..81.00 rows=1 width=0) (actual time=66.263..66.263 rows=0 loops=402)" " -> Bitmap Index Scan on "Product_Category_idx" (cost=0.00..31.12 rows=371 width=0) (actual time=0.188..0.188 rows=337 loops=402)" " Index Cond: ("Category" = "Category".id)" " -> Bitmap Index Scan on "Product_idxfti_idx" (cost=0.00..49.60 rows=113 width=0) (actual time=70.557..70.557 rows=178 loops=376)" " Index Cond: (idxfti @@ '''vpc'''::tsquery)" "Total runtime: 26652.772 ms" ######################################### Similar query: EXPLAIN ANALYZE SELECT *FROM "Product" AS p, (SELECT "id" AS cid FROM "Category" WHERE "lft" BETWEEN 1 AND 792) AS c WHERE p."Category"=c."cid" AND (p."idxfti" @@ to_tsquery('simple', 'vpc')) ORDER BY creationtime ASC OFFSET 0 LIMIT 40 "Limit (cost=180.09..180.09 rows=1 width=738) (actual time=23530.598..23530.730 rows=40 loops=1)" " -> Sort (cost=180.09..180.09 rows=1 width=738) (actual time=23530.593..23530.632 rows=40 loops=1)" " Sort Key: p.creationtime" " Sort Method: top-N heapsort Memory: 68kB" " -> Nested Loop (cost=85.27..180.08 rows=1 width=738) (actual time=10523.533..23530.043 rows=130 loops=1)" " -> Bitmap Heap Scan on "Category" (cost=4.27..10.03 rows=2 width=4) (actual time=0.270..1.688 rows=402 loops=1)" " Recheck Cond: ((lft >= 1) AND (lft <= 792))" " -> Bitmap Index Scan on "Category_lftrgt_idx" (cost=0.00..4.27 rows=2 width=0) (actual time=0.246..0.246 rows=402 loops=1)" " Index Cond: ((lft >= 1) AND (lft <= 792))" " -> Bitmap Heap Scan on "Product" p (cost=81.00..85.01 rows=1 width=734) (actual time=58.512..58.516 rows=0 loops=402)" " Recheck Cond: (("Category" = "Category".id) AND (idxfti @@ '''vpc'''::tsquery))" " -> BitmapAnd (cost=81.00..81.00 rows=1 width=0) (actual time=58.503..58.503 rows=0 loops=402)" " -> Bitmap Index Scan on "Product_Category_idx" (cost=0.00..31.12 rows=371 width=0) (actual time=0.213..0.213 rows=337 loops=402)" " Index Cond: ("Category" = "Category".id)" " -> Bitmap Index Scan on "Product_idxfti_idx" (cost=0.00..49.60 rows=113 width=0) (actual time=62.246..62.246 rows=178 loops=376)" " Index Cond: (idxfti @@ '''vpc'''::tsquery)" "Total runtime: 23531.079 ms"