Re: static virtual columns as result?

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"