Re: static virtual columns as result? - Mailing list pgsql-performance

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"