text search query planning

Hi,

I've a problem understanding of slow query. I use 8.4 for storing
syslog messages, on which i want to do text searching. This table:

syslog=# \d fw_msgs
Table "public.fw_msgs"
Column | Type | Modifiers
----------+------+-----------
datetime | text |
facility | text |
level | text |
host | text |
program | text |
pid | text |
message | text |
Indexes:
"fw_msgs_datetime_idx" btree (datetime)
"fw_msgs_facility_idx" btree (facility)
"fw_msgs_host_idx" btree (host)
"fw_msgs_idx" gin (to_tsvector('english'::regconfig, message))
"fw_msgs_program_idx" btree (program)

contains millions of records.

This query is fast:

syslog=# explain select datetime,facility,level,host,program,pid,message from fw_msgs where to_tsvector('english', message) @@ to_tsquery ('131.211.1.124') limit 3000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=17658.01..25225.08 rows=3000 width=176)
-> Bitmap Heap Scan on fw_msgs (cost=17658.01..181537.99 rows=64971 width=176)
Recheck Cond: (to_tsvector('english'::regconfig, message) @@ to_tsquery('131.211.1.124'::text))
-> Bitmap Index Scan on fw_msgs_idx (cost=0.00..17641.77 rows=64971 width=0)
Index Cond: (to_tsvector('english'::regconfig, message) @@ to_tsquery('131.211.1.124'::text))
(5 rows)

However, when I want to order by datetime, it takes for ever. It seems the db
insist on first sorting on datetime instead of using the gin index:

syslog=# explain select datetime,facility,level,host,program,pid,message from fw_msgs where to_tsvector('english', message) @@ to_tsquery ('131.211.1.124') order by datetime limit 3000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.00..44985.68 rows=3000 width=176)
-> Index Scan using fw_msgs_datetime_idx on fw_msgs (cost=0.00..974644.66 rows=64997 width=176)
Filter: (to_tsvector('english'::regconfig, message) @@ to_tsquery('131.211.1.124'::text))
(3 rows)

doing something like

select datetime,facility,level,host,program,pid,message from fw_msgs where message in (select message from fw_msgs where to_tsvector('english', message) @@ to_tsquery ('131.211.1.124') ) order by datetime desc limit 3000;

doesn't help.

Any ideas?

Regards,
--
Henk van Lingen, (o- -+
Netwerk & Telefonie, ICT Service Center /\ |
Universiteit Utrecht, Jenalaan 18a, kamer 0.12 v_/_
http://henk.vanlingen.net/ http://www.tuxtown.net/netiquette/