hello all,
I'm having troubles getting the following statement to use the index on
'gazette'. If i remove the order by condition it then uses the index. Below is
the explain analyse. The first explain analyse is the one i'm having problems
with. The second explain 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=13991 loops=1)
-> Bitmap Heap Scan on keyword_data k
(cost=29.14..2597.89 rows=994 width=39) (actual time=942.640..1067.716
rows=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.784 rows=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=3 width=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.021 rows=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=35613 loops=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.487 rows=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.614 rows=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=2 width=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.014 rows=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.012 rows=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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
