hello all,
I'm having troubles getting the following statement to use the index on 'gazette'. If i remove the order by condition
itthen uses the index. Below is the explain analyse. The first explain analyse is the one i'm having problems with. The
secondexplain analyse is from a different database that has the same structure as the first, but does not hold as much
data.
Both databases reside on the same server - Postgres 8.3.3
Table definitions are below.
Tables have been analysed, i'm still a novice at reading these query plans so if anyone has any ideas it would be much
appreciated
gazette=# explain analyse SELECT
k.keyword,p.page_no,k.subtopic
FROM
keyword_data k,pages_new p,keyword_page_linkup kp,gazette g
WHERE
g.id = p.gazette
AND kp.keyword_id = k.id
AND kp.page_id = p.id
AND idxfti @@ to_tsquery('english', 'water&!supply&!inspector&!officer&!clerk')
ORDER BY
g.gaz_date ASC,
g.gaz_no ASC
limit 20 OFFSET 60;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=12243.81..12243.86 rows=20 width=46) (actual time=2019.555..2019.573 rows=20 loops=1)
-> Sort (cost=12243.66..12247.81 rows=1661 width=46) (actual time=2019.513..2019.547 rows=80 loops=1)
Sort Key: g.gaz_date, g.gaz_no
Sort Method: top-N heapsort Memory: 26kB
-> Hash Join (cost=1437.43..12182.85 rows=1661 width=46) (actual time=1122.213..1974.885 rows=13991 loops=1)
Hash Cond: (p.gazette = g.id)
-> Nested Loop (cost=29.14..10735.11 rows=1661 width=43) (actual time=942.933..1739.010 rows=13991
loops=1)
-> Nested Loop (cost=29.14..8588.65 rows=1661 width=39) (actual time=942.825..1401.104
rows=13991loops=1)
-> Bitmap Heap Scan on keyword_data k (cost=29.14..2597.89 rows=994 width=39) (actual
time=942.640..1067.716rows=7513 loops=1)
Filter: (idxfti @@ '''water'' & !''suppli'' & !''inspector'' & !''offic'' &
!''clerk'''::tsquery)
-> Bitmap Index Scan on idxfti_idx (cost=0.00..28.89 rows=994 width=0) (actual
time=940.784..940.784rows=7514 loops=1)
Index Cond: (idxfti @@ '''water'' & !''suppli'' & !''inspector'' & !''offic'' &
!''clerk'''::tsquery)
-> Index Scan using keyword_page_linkup_idx on keyword_page_linkup kp (cost=0.00..5.99
rows=3width=8) (actual time=0.036..0.040 rows=2 loops=7513)
Index Cond: (kp.keyword_id = k.id)
-> Index Scan using pages_new_pkey on pages_new p (cost=0.00..1.28 rows=1 width=12) (actual
time=0.018..0.021rows=1 loops=13991)
Index Cond: (p.id = kp.page_id)
-> Hash (cost=963.13..963.13 rows=35613 width=11) (actual time=179.166..179.166 rows=35613 loops=1)
-> Seq Scan on gazette g (cost=0.00..963.13 rows=35613 width=11) (actual time=0.085..137.694
rows=35613loops=1)
Total runtime: 2019.933 ms
(19 rows)
govt_gazette=# explain analyse SELECT
k.keyword,p.page_no,k.subtopic
FROM
keyword_data k,pages_new p,keyword_page_linkup kp,gazette g
WHERE
g.id = p.gazette
AND kp.keyword_id = k.id
AND kp.page_id = p.id
AND idxfti @@ to_tsquery('english', 'water&!supply&!inspector&!officer&!clerk')
ORDER BY
g.gaz_date ASC,
g.gaz_no ASC
limit 20 OFFSET 60;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2758.13..2758.18 rows=20 width=49) (actual time=127.582..127.596 rows=20 loops=1)
-> Sort (cost=2757.98..2758.84 rows=344 width=49) (actual time=127.544..127.568 rows=80 loops=1)
Sort Key: g.gaz_date, g.gaz_no
Sort Method: top-N heapsort Memory: 23kB
-> Nested Loop (cost=14.16..2745.38 rows=344 width=49) (actual time=34.196..120.869 rows=1777 loops=1)
-> Nested Loop (cost=14.16..2640.70 rows=344 width=46) (actual time=34.143..92.118 rows=1777 loops=1)
-> Nested Loop (cost=14.16..2539.09 rows=344 width=42) (actual time=34.092..62.106 rows=1777
loops=1)
-> Bitmap Heap Scan on keyword_data k (cost=14.16..593.02 rows=231 width=42) (actual
time=33.937..39.487rows=975 loops=1)
Recheck Cond: (idxfti @@ '''water'' & !''suppli'' & !''inspector'' & !''offic'' &
!''clerk'''::tsquery)
-> Bitmap Index Scan on idxfti_idx (cost=0.00..14.10 rows=231 width=0) (actual
time=33.614..33.614rows=975 loops=1)
Index Cond: (idxfti @@ '''water'' & !''suppli'' & !''inspector'' & !''offic'' &
!''clerk'''::tsquery)
-> Index Scan using keyword_page_linkup_idx on keyword_page_linkup kp (cost=0.00..8.40
rows=2width=8) (actual time=0.016..0.019 rows=2 loops=975)
Index Cond: (kp.keyword_id = k.id)
-> Index Scan using pages_new_pkey on pages_new p (cost=0.00..0.28 rows=1 width=12) (actual
time=0.012..0.014rows=1 loops=1777)
Index Cond: (p.id = kp.page_id)
-> Index Scan using gazette_pkey on gazette g (cost=0.00..0.29 rows=1 width=11) (actual
time=0.010..0.012rows=1 loops=1777)
Index Cond: (g.id = p.gazette)
Total runtime: 127.966 ms
(18 rows)
Below are the relevant table definitions
gazette=# \d gazette
Table "public.gazette"
Column | Type | Modifiers
------------------+-------------------+------------------------------------------------------
year | integer |
doctype | character varying |
ggtype | character varying |
old_vol | character varying |
vol | integer | default 0
sequence | integer | default 0
pagerange | character varying |
year_start_page | integer | default 0
year_finish_page | integer | default 0
ggtype_display | character varying |
lr_type | character varying |
gaz_start_page | integer | default 0
style | character varying |
missing | character varying |
gaz_date | date |
ref_gaz_date | date |
page_qty | character varying |
gaz_no | character varying |
remarks | character varying |
type | character varying |
cat_no | character varying |
page_of_pgs | character varying |
day | character varying |
web_remarks | character varying |
id | integer | not null default nextval('gazette_id_seq'::regclass)
Indexes:
"gazette_pkey" PRIMARY KEY, btree (id)
"gazette_idx" btree (year)
"gazette_idx1" btree (year, doctype, ggtype)
"gazette_idx2" btree (year, doctype, ggtype, lr_type)
"gazette_idx3" btree (gaz_date)
gazette=# \d pages_new
Table "public.pages_new"
Column | Type | Modifiers
---------+-------------------+--------------------------------------------------------
id | integer | not null default nextval('pages_new_id_seq'::regclass)
page_no | character varying |
gazette | integer |
Indexes:
"pages_new_pkey" PRIMARY KEY, btree (id)
"pages_new_idx" UNIQUE, btree (page_no, gazette)
"pages_new_idx1" btree (gazette)
Foreign-key constraints:
"pages_new_fk" FOREIGN KEY (gazette) REFERENCES gazette(id) ON UPDATE CASCADE ON DELETE CASCADE
gazette=# \d keyword_data
Table "public.keyword_data"
Column | Type | Modifiers
----------+-------------------+-----------------------------------------------------------
id | integer | not null default nextval('keyword_data_id_seq'::regclass)
keyword | character varying | not null
category | integer | not null
subtopic | character varying |
idxfti | tsvector |
Indexes:
"keyword_data_pkey" PRIMARY KEY, btree (id)
"idxfti_idx" gist (idxfti)
"keyword_data_idx" btree (category)
"keyword_data_idx1" btree (keyword)
Foreign-key constraints:
"keyword_data_fk" FOREIGN KEY (category) REFERENCES categorys(categoryid) ON UPDATE CASCADE ON DELETE CASCADE
gazette=# \d keyword_page_linkup
Table "public.keyword_page_linkup"
Column | Type | Modifiers
------------+---------+------------------------------------------------------------------
id | integer | not null default nextval('keyword_page_linkup_id_seq'::regclass)
keyword_id | integer | not null
page_id | integer |
Indexes:
"keyword_page_linkup_pkey" PRIMARY KEY, btree (id)
"keyword_page_linkup_idx" btree (keyword_id)
"keyword_page_linkup_idx1" btree (page_id)
Foreign-key constraints:
"keyword_page_linkup_fk" FOREIGN KEY (keyword_id) REFERENCES keyword_data(id) ON UPDATE CASCADE ON DELETE CASCADE
"keyword_page_linkup_fk1" FOREIGN KEY (page_id) REFERENCES pages_new(id) ON UPDATE CASCADE ON DELETE CASCADE
--
Troy Rasiah