[PERFORM] static virtual columns as result?

[PV]

Hello,

I "have" this two tables:


Table Cat:
id|A|B
--+-+-
1|3|5
2|5|8
3|6|9

Table Pr:
id|Catid|
--+-
1|3
2|2
3|1


I need replace "Catid" column for corresponding values A and B (Table 
Cat) in Table Pr.


Virtual table like this:
Table Pr:
id|Catid|A|B
--+-+-+
1|3|6|9
2|2|5|8
3|1|3|5


Something like this, but that works,...

SELECT * FROM pr WHERE pr.a /*> 1 AND*/*//* 
 
pr.b < 10;


With subqueries is too slow:
SELECT * FROM "Pr" AS p, (SELECT "id" AS cid FROM "Cat" WHERE "lft" > 1 
AND "rgt" < 10) AS c WHERE p."Cat"=c."cid" AND (...)) ORDER BY "Catid" 
ASC OFFSET 0 LIMIT 40



Any suggestion?

Re: [PERFORM] static virtual columns as result?

[PV]

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

Re: [PERFORM] static virtual columns as result?

[PV]

SOLVED:

1) Try remove old functions tsearch, ltree,..., "old" database format
2) Vacuum or rebuild database

One could solve the problem.

Regards

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance