Skip site navigation (1) Skip section navigation (2)

Full Text index is not using during OR operation for multiple table join

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Full Text index is not using during OR operation for multiple table join
Date: 2010-11-29 10:27:05
Message-ID: AANLkTinhPS3dF48sMAKWdf-8RU5TzsDq_i79o7MakxtE@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Full Text index is not using during OR operation for multiple table join:
explain
SELECT crmentity.crmid, crmentity.setype, crmentity.modifiedtime,
activity.subject,case when ( users.user_name not like '') then
users.user_name else groups.groupname end as user_name, activity.date_start
FROM crmentity INNER JOIN activity ON crmentity.crmid = activity.activityid
and crmentity.deleted = 0
LEFT JOIN activitygrouprelation ON activitygrouprelation.activityid =
crmentity.crmid
LEFT JOIN groups ON groups.groupname = activitygrouprelation.groupname
LEFT join users ON crmentity.smownerid= users.id
WHERE
to_tsvector(' en', for_fts( activity.subject)) @@ to_tsquery(' en',
replace(' Dhaka University of Bangladesh:*', ' ',':* & '))
or
to_tsvector(' en', for_fts( crmentity.description)) @@ to_tsquery(' en',
replace(' Dhaka University of Bangladesh:*', ' ',':* & '))
ORDER BY crmentity.modifiedtime DESC LIMIT 100

  QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=112724.54..112724.54 rows=1 width=99)
  -> Sort (cost=112724.54..112724.54 rows=1 width=99)
  Sort Key: crmentity.modifiedtime
  -> Nested Loop Left Join (cost=0.00..112724.53 rows=1 width=99)
  -> Nested Loop Left Join (cost=0.00..112724.24 rows=1 width=82)
  -> Nested Loop Left Join (cost=0.00..112723.96 rows=1 width=79)
  -> Nested Loop (cost=0.00..112723.68 rows=1 width=56)
  Join Filter: ((to_tsvector('en'::regconfig,
regexp_replace((activity.subject)::text,
'(&[^;]+;)|(<[^>]+>)|([\\s\\r\\n\\t]+)'::text, ''::text, 'gs'::text)) @@ '''
Dhaka'':* & ''univers'':* & ''bangladesh'':*'::tsquery) OR
(to_tsvector('en'::regconfig, regexp_replace(crmentity.description,
'(&[^;]+;)|(<[^>]+>)|([\\s\\r\\n\\t]+)'::text, ' '::text, 'gs'::text)) @@
''' Dhaka'':* & ''univers'':* & ''bangladesh'':*'::tsquery))
  -> Index Scan using activity_pkey on activity (cost=0.00..10223.89
rows=343070 width=36)
  -> Index Scan using crmentity_pkey on crmentity (cost=0.00..0.27 rows=1
width=151)
  Index Cond: (crmentity.crmid = activity.activityid)
  Filter: (crmentity.deleted = 0)
  -> Index Scan using activitygrouprelation_activityid_idx on
activitygrouprelation (cost=0.00..0.27 rows=1 width=27)
  Index Cond: (activitygrouprelation.activityid = crmentity.crmid)
  -> Index Scan using groups_groupname_idx on groups (cost=0.00..0.27 rows=1
width=26)
  Index Cond: ((groups.groupname)::text =
(activitygrouprelation.groupname)::text)
  -> Index Scan using users_pkey on users (cost=0.00..0.27 rows=1 width=25)
  Index Cond: (crmentity.smownerid = users.id)


The above query are not using fts indexes, even hang the server.

But,


explain
SELECT crmentity.crmid, crmentity.setype, crmentity.modifiedtime,
activity.subject,case when ( users.user_name not like '') then
users.user_name else groups.groupname end as user_name, activity.date_start
FROM crmentity INNER JOIN activity ON crmentity.crmid = activity.activityid
and crmentity.deleted = 0
LEFT JOIN activitygrouprelation ON activitygrouprelation.activityid =
crmentity.crmid
LEFT JOIN groups ON groups.groupname = activitygrouprelation.groupname
LEFT join users ON crmentity.smownerid= users.id
WHERE
to_tsvector(' en', for_fts( activity.subject)) @@ to_tsquery(' en',
replace(' Dhaka University of Bangladesh:*', ' ',':* & '))
ORDER BY crmentity.modifiedtime DESC LIMIT 100

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Limit (cost=1.46..1.47 rows=1 width=99) (actual time=0.824..0.824 rows=0
loops=1)
  -> Sort (cost=1.46..1.47 rows=1 width=99) (actual time=0.819..0.819 rows=0
loops=1)
  Sort Key: crmentity.modifiedtime
  Sort Method: quicksort Memory: 17kB
  -> Nested Loop Left Join (cost=0.27..1.45 rows=1 width=99) (actual
time=0.752..0.752 rows=0 loops=1)
  -> Nested Loop Left Join (cost=0.27..1.17 rows=1 width=82) (actual
time=0.750..0.750 rows=0 loops=1)
  -> Nested Loop Left Join (cost=0.27..0.88 rows=1 width=79) (actual
time=0.748..0.748 rows=0 loops=1)
  -> Nested Loop (cost=0.27..0.60 rows=1 width=56) (actual time=0.746..0.746
rows=0 loops=1)
  -> Bitmap Heap Scan on activity (cost=0.27..0.30 rows=1 width=36) (actual
time=0.744..0.744 rows=0 loops=1)
  Recheck Cond: (to_tsvector('en'::regconfig,
regexp_replace((subject)::text,
'(&[^;]+;)|(<[^>]+>)|([\\s\\r\\n\\t]+)'::text, ' '::text,
'gs'::text)) @@ ''' Dhaka'':* & ''univers'':* & ''bangladesh'':*'::tsquery)
  -> Bitmap Index Scan on ftx_en_activity_subject (cost=0.00..0.27 rows=1
width=0) (actual time=0.740..0.740 rows=0 loops=1)
  Index Cond: (to_tsvector('en'::regconfig, regexp_replace((subject)::text,
'(&[^;]+;)|(<[^>]+>)|([\\s\\r\\n\\t]+)'::text, ' '::te
xt, 'gs'::text)) @@ ''' Dhaka'':* & ''univers'':* &
''bangladesh'':*'::tsquery)
  -> Index Scan using crmentity_pkey on crmentity (cost=0.00..0.29 rows=1
width=24) (never executed)
  Index Cond: (crmentity.crmid = activity.activityid)
  Filter: (crmentity.deleted = 0)
  -> Index Scan using activitygrouprelation_activityid_idx on
activitygrouprelation (cost=0.00..0.27 rows=1 width=27) (never executed)
  Index Cond: (activitygrouprelation.activityid = crmentity.crmid)
  -> Index Scan using groups_groupname_idx on groups (cost=0.00..0.27 rows=1
width=26) (never executed)
  Index Cond: ((groups.groupname)::text =
(activitygrouprelation.groupname)::text)
  -> Index Scan using users_pkey on users (cost=0.00..0.27 rows=1 width=25)
(never executed)
  Index Cond: (crmentity.smownerid = users.id)
 Total runtime: 1.188 ms




explain
SELECT crmentity.crmid, crmentity.setype, crmentity.modifiedtime,
activity.subject,case when ( users.user_name not like '') then
users.user_name else groups.groupname end as user_name, activity.date_start
FROM crmentity INNER JOIN activity ON crmentity.crmid = activity.activityid
and crmentity.deleted = 0
LEFT JOIN activitygrouprelation ON activitygrouprelation.activityid =
crmentity.crmid
LEFT JOIN groups ON groups.groupname = activitygrouprelation.groupname
LEFT join users ON crmentity.smownerid= users.id
WHERE
to_tsvector(' en', for_fts( crmentity.description)) @@ to_tsquery(' en',
replace(' Dhaka University of Bangladesh:*', ' ',':* & '))
ORDER BY crmentity.modifiedtime DESC LIMIT 100

  QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Limit (cost=1.50..1.51 rows=1 width=99) (actual time=5.044..5.047 rows=1
loops=1)
  -> Sort (cost=1.50..1.51 rows=1 width=99) (actual time=5.041..5.042 rows=1
loops=1)
  Sort Key: crmentity.modifiedtime
  Sort Method: quicksort Memory: 17kB
  -> Nested Loop Left Join (cost=0.27..1.49 rows=1 width=99) (actual
time=4.998..5.012 rows=1 loops=1)
  -> Nested Loop Left Join (cost=0.27..1.19 rows=1 width=82) (actual
time=4.952..4.961 rows=1 loops=1)
  -> Nested Loop Left Join (cost=0.27..0.90 rows=1 width=79) (actual
time=4.949..4.956 rows=1 loops=1)
  -> Nested Loop (cost=0.27..0.60 rows=1 width=56) (actual time=4.943..4.948
rows=1 loops=1)
  -> Bitmap Heap Scan on crmentity (cost=0.27..0.30 rows=1 width=24) (actual
time=4.727..4.799 rows=3 loops=1)
  Recheck Cond: (to_tsvector('en'::regconfig, regexp_replace(description,
'(&[^;]+;)|(<[^>]+>)|([\\s\\r\\n\\t]+)'::text, ' '::text, 'gs'
::text)) @@ ''' Dhaka'':* & ''univers'':* & ''bangladesh'':*'::tsquery)
  Filter: (deleted = 0)
  -> Bitmap Index Scan on ftx_en_crmentity_description (cost=0.00..0.27
rows=1 width=0) (actual time=4.687..4.687 rows=3 loops=1)
  Index Cond: (to_tsvector('en'::regconfig, regexp_replace(description,
'(&[^;]+;)|(<[^>]+>)|([\\s\\r\\n\\t]+)'::text, ' '::text,
'gs'::text)) @@ ''' Dhaka'':* & ''univers'':* & ''bangladesh'':*'::tsquery)
  -> Index Scan using activity_pkey on activity (cost=0.00..0.29 rows=1
width=36) (actual time=0.043..0.043 rows=0 loops=3)
  Index Cond: (activity.activityid = crmentity.crmid)
  -> Index Scan using activitygrouprelation_activityid_idx on
activitygrouprelation (cost=0.00..0.29 rows=1 width=27) (actual
time=0.003..0.003
rows=0 loops=1)
  Index Cond: (activitygrouprelation.activityid = crmentity.crmid)
  -> Index Scan using groups_groupname_idx on groups (cost=0.00..0.27 rows=1
width=26) (actual time=0.001..0.001 rows=0 loops=1)
  Index Cond: ((groups.groupname)::text =
(activitygrouprelation.groupname)::text)
  -> Index Scan using users_pkey on users (cost=0.00..0.29 rows=1 width=25)
(actual time=0.033..0.035 rows=1 loops=1)
  Index Cond: (crmentity.smownerid = users.id)
 Total runtime: 5.229 ms
(22 rows)



\d crmentity
  Table "public.crmentity"
  Column | Type | Modifiers
--------------+-----------------------------+--------------------
 crmid | integer | not null
 smcreatorid | integer | not null default 0
 smownerid | integer | not null default 0
 modifiedby | integer | not null default 0
 setype | character varying(30) | not null
 description | text |
 createdtime | timestamp without time zone | not null
 modifiedtime | timestamp without time zone | not null
 viewedtime | timestamp without time zone |
 status | character varying(50) |
 version | integer | not null default 0
 presence | integer | default 1
 deleted | integer | not null default 0
Indexes:
  "crmentity_pkey" PRIMARY KEY, btree (crmid)
  "crmentity_createdtime_idx" btree (createdtime)
  "crmentity_modifiedby_idx" btree (modifiedby)
  "crmentity_modifiedtime_idx" btree (modifiedtime)
  "crmentity_smcreatorid_idx" btree (smcreatorid)
  "crmentity_smownerid_idx" btree (smownerid)
  "ftx_en_crmentity_description" gin (to_tsvector('vcrm_en'::regconfig,
for_fts(description)))
  "crmentity_deleted_idx" btree (deleted)
Referenced by:
  TABLE "service" CONSTRAINT "fk_1_service" FOREIGN KEY (serviceid)
REFERENCES crmentity(crmid) ON DELETE CASCADE
  TABLE "cc2crmentity" CONSTRAINT "fk_cc2crmentity_crmentity" FOREIGN KEY
(crm_id) REFERENCES crmentity(crmid) ON UPDATE CASCADE ON DELETE CASCADE


\d activity

  Table "public.activity"
  Column | Type | Modifiers
------------------+------------------------+-------------------------------------------
 activityid | integer | not null default 0
 subject | character varying(250) | not null
 semodule | character varying(20) |
 activitytype | character varying(200) | not null
 date_start | date | not null
 due_date | date |
 time_start | character varying(50) |
 time_end | character varying(50) |
 sendnotification | character varying(3) | not null default '0'::character
varying
 duration_hours | character varying(2) |
 duration_minutes | character varying(200) |
 status | character varying(200) |
 eventstatus | character varying(200) |
 priority | character varying(200) |
 location | character varying(150) |
 notime | character varying(3) | not null default '0'::character varying
 visibility | character varying(50) | not null default 'all'::character
varying
 recurringtype | character varying(200) |
 end_date | date |
 end_time | character varying(50) |
Indexes:
  "activity_pkey" PRIMARY KEY, btree (activityid)
  "activity_activitytype_idx" btree (activitytype)
  "activity_date_start_idx" btree (date_start)
  "activity_due_date_idx" btree (due_date)
  "activity_eventstatus_idx" btree (eventstatus)
  "activity_status_idx" btree (status)
  "activity_subject_idx" btree (subject)
  "activity_time_start_idx" btree (time_start)
  "ftx_en_activity_subject" gin (to_tsvector('vcrm_en'::regconfig,
for_fts(subject::text)))

Responses

pgsql-bugs by date

Next:From: Michael MeskesDate: 2010-11-29 10:55:33
Subject: Re: BUG #5762: ecpg
Previous:From: Robert HaasDate: 2010-11-29 02:11:35
Subject: Re: BUG #5763: pg_hba.conf not honored

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group