Query not using index - Mailing list pgsql-general

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