Date: Sun, 1 May 2011 03:23:52 -0700 (PDT)
From: Mark <Marek.Balgar@seznam.cz>
To: pgsql-performance@postgresql.org
Message-ID: <1304245432864-4362578.post@n5.nabble.com>
Subject: Query improvement
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi I have 3 tables 
page - revision - pagecontent

CREATE TABLE mediawiki.page
(
  page_id serial NOT NULL,
  page_namespace smallint NOT NULL,
  page_title text NOT NULL,
  page_restrictions text,
  page_counter bigint NOT NULL DEFAULT 0,
  page_is_redirect smallint NOT NULL DEFAULT 0,
  page_is_new smallint NOT NULL DEFAULT 0,
  page_random numeric(15,14) NOT NULL DEFAULT random(),
  page_touched timestamp with time zone,
  page_latest integer NOT NULL,
  page_len integer NOT NULL,
  titlevector tsvector,
  page_type integer NOT NULL DEFAULT 0,
  CONSTRAINT page_pkey PRIMARY KEY (page_id)
);

CREATE TABLE mediawiki.revision
(
  rev_id serial NOT NULL,
  rev_page integer,
  rev_text_id integer,
  rev_comment text,
  rev_user integer NOT NULL,
  rev_user_text text NOT NULL,
  rev_timestamp timestamp with time zone NOT NULL,
  rev_minor_edit smallint NOT NULL DEFAULT 0,
  rev_deleted smallint NOT NULL DEFAULT 0,
  rev_len integer,
  rev_parent_id integer,
  CONSTRAINT revision_rev_page_fkey FOREIGN KEY (rev_page)
      REFERENCES mediawiki.page (page_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT revision_rev_id_key UNIQUE (rev_id)
)

CREATE TABLE mediawiki.pagecontent
(
  old_id integer NOT NULL DEFAULT
nextval('mediawiki.text_old_id_seq'::regclass),
  old_text text,
  old_flags text,
  textvector tsvector,
  CONSTRAINT pagecontent_pkey PRIMARY KEY (old_id)
)

where i have query 
SELECT pa.page_id, pa.page_title, 
ts_rank(pc.textvector,(to_tsquery('fotbal')))+ts_rank(pa.titlevector,(to_tsquery('fotbal')))*10
as totalrank 
	from mediawiki.page pa, mediawiki.revision re, mediawiki.pagecontent pc 
	WHERE pa.page_id in 
		(SELECT page_id FROM mediawiki.page WHERE page_id IN
		(SELECT page_id FROM mediawiki.page 
			 WHERE (titlevector @@ (to_tsquery('fotbal'))))
		OR page_id IN
		(SELECT p.page_id from mediawiki.page p,mediawiki.revision r,
		(SELECT old_id FROM mediawiki.pagecontent 
		WHERE (textvector @@ (to_tsquery('fotbal')))) ss
		WHERE (p.page_id=r.rev_page AND r.rev_id=ss.old_id)))
	AND (pa.page_id=re.rev_page AND re.rev_id=pc.old_id)
	ORDER BY totalrank LIMIT 100;

This query find out titles of pages in page and content in page content by
full text search - @@ 
afterwards i count for the resulted id  by ts_rank the relevance.

Now the problem. 
When I try ANALYZE it shows:
"Limit  (cost=136568.00..136568.25 rows=100 width=185)"
"  ->  Sort  (cost=136568.00..137152.26 rows=233703 width=185)"
"        Sort Key: ((ts_rank(pc.textvector, to_tsquery('fotbal'::text)) +
(ts_rank(pa.titlevector, to_tsquery('fotbal'::text)) * 10::double
precision)))"
"        ->  Hash Join  (cost=61707.99..127636.04 rows=233703 width=185)"
"              Hash Cond: (re.rev_id = pc.old_id)"
"              ->  Merge Join  (cost=24098.90..71107.48 rows=233703
width=66)"
"                    Merge Cond: (pa.page_id = re.rev_page)"
"                    ->  Merge Semi Join  (cost=24096.98..55665.69
rows=233703 width=66)"
"                          Merge Cond: (pa.page_id =
mediawiki.page.page_id)"
"                          ->  Index Scan using page_btree_id on page pa 
(cost=0.00..13155.20 rows=311604 width=62)"
"                          ->  Index Scan using page_btree_id on page 
(cost=24096.98..38810.19 rows=233703 width=4)"
"                                Filter: ((hashed SubPlan 1) OR (hashed
SubPlan 2))"
"                                SubPlan 1"
"                                  ->  Bitmap Heap Scan on page 
(cost=10.41..900.33 rows=270 width=4)"
"                                        Recheck Cond: (titlevector @@
to_tsquery('fotbal'::text))"
"                                        ->  Bitmap Index Scan on gin_index 
(cost=0.00..10.34 rows=270 width=0)"
"                                              Index Cond: (titlevector @@
to_tsquery('fotbal'::text))"
"                                SubPlan 2"
"                                  ->  Nested Loop  (cost=1499.29..23192.08
rows=1558 width=4)"
"                                        ->  Nested Loop 
(cost=1499.29..15967.11 rows=1558 width=4)"
"                                              ->  Bitmap Heap Scan on
pagecontent  (cost=1499.29..6448.12 rows=1558 width=4)"
"                                                    Recheck Cond:
(textvector @@ to_tsquery('fotbal'::text))"
"                                                    ->  Bitmap Index Scan
on gin_index2  (cost=0.00..1498.90 rows=1558 width=0)"
"                                                          Index Cond:
(textvector @@ to_tsquery('fotbal'::text))"
"                                              ->  Index Scan using
page_btree_rev_content_id on revision r  (cost=0.00..6.10 rows=1 width=8)"
"                                                    Index Cond: (r.rev_id =
pagecontent.old_id)"
"                                        ->  Index Scan using page_btree_id
on page p  (cost=0.00..4.62 rows=1 width=4)"
"                                              Index Cond: (p.page_id =
r.rev_page)"
"                    ->  Index Scan using page_btree_rev_page_id on revision
re  (cost=0.00..11850.52 rows=311604 width=8)"
"              ->  Hash  (cost=27932.04..27932.04 rows=311604 width=127)"
"                    ->  Seq Scan on pagecontent pc  (cost=0.00..27932.04
rows=311604 width=127)"


I there some posibility to speed up the hash join which takes a lot of time?
I have tried to find some solution, but it was not successfull.
Thanks a lot.--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-improvement-tp4362578p4362578.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.