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)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
