Partitioned tabled not using indexes for full text search - Mailing list pgsql-general

I have a table that is partitioned on a daily basis.

Full text searches used to be respectably fast with large tables (40
million + records) but insert speed would slow down.  So I went with a
partitioned approach. But now, it doesn't seem like the indexes are
being used.

Any idea why it would not be using the indexes?

Here are appropriate descriptions and Explains:

syslog=# \d systemevents;
                 Table "public.systemevents"
       Column       |            Type             | Modifiers
--------------------+-----------------------------+-----------
 message            | character varying           |
 facility           | integer                     |
 fromhost           | character varying(80)       |
 priority           | integer                     |
 devicereportedtime | timestamp without time zone |
 receivedat         | timestamp without time zone |
 infounitid         | integer                     |
 syslogtag          | character varying(80)       |
 message_index_col  | tsvector                    |
Rules:
    systemevents_insert_032509 AS
    ON INSERT TO systemevents
   WHERE new.devicereportedtime > '2009-03-24 23:59:59'::timestamp
without time zone AND new.devicereportedtime <= '2009-03-25
23:59:59'::timestamp without time zone DO INSTEAD  INSERT INTO
systemevents_032509 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
  VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
    systemevents_insert_032609 AS
    ON INSERT TO systemevents
   WHERE new.devicereportedtime > '2009-03-25 23:59:59'::timestamp
without time zone AND new.devicereportedtime <= '2009-03-26
23:59:59'::timestamp without time zone DO INSTEAD  INSERT INTO
systemevents_032609 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
  VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
    systemevents_insert_032709 AS
    ON INSERT TO systemevents
   WHERE new.devicereportedtime > '2009-03-26 23:59:59'::timestamp
without time zone AND new.devicereportedtime <= '2009-03-27
23:59:59'::timestamp without time zone DO INSTEAD  INSERT INTO
systemevents_032709 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
  VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
    systemevents_insert_032809 AS
    ON INSERT TO systemevents
   WHERE new.devicereportedtime > '2009-03-27 23:59:59'::timestamp
without time zone AND new.devicereportedtime <= '2009-03-28
23:59:59'::timestamp without time zone DO INSTEAD  INSERT INTO
systemevents_032809 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
  VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)

syslog=# EXPLAIN ANALYZE SELECT DeviceReportedTime,
REPLACE(REPLACE(Message,'<','<'),'>','>') as Message, Facility,
FromHost, Priority, ReceivedAt, InfoUnitID ,SysLogTag FROM
SystemEvents WHERE message_index_col @@ to_tsquery('english', 'MAIL')
ORDER BY DeviceReportedTime DESC LIMIT 25 OFFSET 0;

    QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=61548.87..61548.93 rows=25 width=176) (actual
time=31933.287..31933.425 rows=25 loops=1)
   ->  Sort  (cost=61548.87..61551.59 rows=1091 width=176) (actual
time=31933.280..31933.327 rows=25 loops=1)
         Sort Key: public.systemevents.devicereportedtime
         Sort Method:  top-N heapsort  Memory: 29kB
         ->  Result  (cost=0.00..61518.08 rows=1091 width=176) (actual
time=43.351..28941.144 rows=21307 loops=1)
               ->  Append  (cost=0.00..61512.62 rows=1091 width=176)
(actual time=43.337..23706.264 rows=21307 loops=1)
                     ->  Seq Scan on systemevents  (cost=0.00..13.00
rows=1 width=151) (actual time=0.007..0.007 rows=0 loops=1)
                           Filter: (message_index_col @@ '''mail'''::tsquery)
                     ->  Seq Scan on systemevents_032609 systemevents
(cost=0.00..27869.42 rows=494 width=152) (actual time=43.318..6153.645
rows=9309 loops=1)
                           Filter: (message_index_col @@ '''mail'''::tsquery)
                     ->  Seq Scan on systemevents_032509 systemevents
(cost=0.00..19001.65 rows=339 width=153) (actual time=0.611..5861.674
rows=6239 loops=1)
                           Filter: (message_index_col @@ '''mail'''::tsquery)
                     ->  Seq Scan on systemevents_032709 systemevents
(cost=0.00..14614.30 rows=256 width=153) (actual time=0.017..4062.849
rows=5759 loops=1)
                           Filter: (message_index_col @@ '''mail'''::tsquery)
                     ->  Seq Scan on systemevents_032809 systemevents
(cost=0.00..14.25 rows=1 width=176) (actual time=0.003..0.003 rows=0
loops=1)
                           Filter: (message_index_col @@ '''mail'''::tsquery)
 Total runtime: 32326.296 ms
(17 rows)


syslog=# \d systemevents_032509;
              Table "public.systemevents_032509"
       Column       |            Type             | Modifiers
--------------------+-----------------------------+-----------
 message            | character varying           |
 facility           | integer                     |
 fromhost           | character varying(80)       |
 priority           | integer                     |
 devicereportedtime | timestamp without time zone |
 receivedat         | timestamp without time zone |
 infounitid         | integer                     |
 syslogtag          | character varying(80)       |
 message_index_col  | tsvector                    |
Indexes:
    "systemevents_msg_idx_032509" btree (message_index_col)
Check constraints:
    "systemevents_032509_devicereportedtime_check" CHECK
(devicereportedtime > '2009-03-24 23:59:59'::timestamp without time
zone AND devicereportedtime <= '2009-03-25 23:59:59'::timestamp
without time zone)
Inherits: systemevents

syslog=# EXPLAIN ANALYZE SELECT DeviceReportedTime,
REPLACE(REPLACE(Message,'<','<'),'>','>') as Message, Facility,
FromHost, Priority, ReceivedAt, InfoUnitID ,SysLogTag FROM
SystemEvents_032509 WHERE message_index_col @@ to_tsquery('english',
'MAIL')  ORDER BY DeviceReportedTime DESC LIMIT 25 OFFSET 0;
                                                               QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=19012.91..19012.97 rows=25 width=153) (actual
time=194408.147..194408.299 rows=25 loops=1)
   ->  Sort  (cost=19012.91..19013.76 rows=339 width=153) (actual
time=194408.138..194408.192 rows=25 loops=1)
         Sort Key: devicereportedtime
         Sort Method:  top-N heapsort  Memory: 29kB
         ->  Seq Scan on systemevents_032509  (cost=0.00..19003.35
rows=339 width=153) (actual time=0.688..98662.260 rows=6239 loops=1)
               Filter: (message_index_col @@ '''mail'''::tsquery)
 Total runtime: 194408.443 ms
(7 rows)