Forcing the right queryplan

Hi,

I've the problem my database is not using the 'right' queryplan in all
cases. Is there a way I can force that and/or how should I tuned the
table statistics?

I'm doing a rsyslog database in PostgreSQL with millions of records
(firewall logging). The db scheme is the so called 'MonitorWare' scheme,
to wich I added two extra indexes.

syslog=# select version();
version

--------------------------------------------------------------------------------
----------------------------------
PostgreSQL 8.4.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20
080704 (Red Hat 4.1.2-48), 64-bit

syslog=# \d systemevents
Table "public.systemevents"
Column | Type | Modi
fiers
--------------------+-----------------------------+-----------------------------
------------------------------
id | integer | not null default nextval('sy
stemevents_id_seq'::regclass)
customerid | bigint |
receivedat | timestamp without time zone |
devicereportedtime | timestamp without time zone |
facility | smallint |
priority | smallint |
fromhost | character varying(60) |
message | text |
ntseverity | integer |
importance | integer |
eventsource | character varying(60) |
eventuser | character varying(60) |
eventcategory | integer |
eventid | integer |
eventbinarydata | text |
maxavailable | integer |
currusage | integer |
minusage | integer |
maxusage | integer |
infounitid | integer |
syslogtag | character varying(60) |
eventlogtype | character varying(60) |
genericfilename | character varying(60) |
systemid | integer |
Indexes:
"systemevents_pkey" PRIMARY KEY, btree (id)
"fromhost_idx" btree (fromhost)
"msgs_idx" gin (to_tsvector('english'::regconfig, message))

The GIN index is to do text searching (via LogAnalyzer).

Now there are two types of query plans:

syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, infounitid, message FROM systemevents WHERE ( ( to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) ) ORDER BY id DESC LIMIT 100; QUERY PLAN

--------------------------------------------------------------------------------
---------------------------------
Limit (cost=0.00..10177.22 rows=100 width=159)
-> Index Scan Backward using systemevents_pkey on systemevents (cost=0.00..
1052934.86 rows=10346 width=159)
Filter: (to_tsvector('english'::regconfig, message) @@ to_tsquery('131.
211.112.9'::text))
(3 rows)

This one is useless (takes very long). However this one:

syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, infounitid, message FROM systemevents WHERE ( ( to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) ) ORDER BY id DESC LIMIT 500;
QUERY PLAN

--------------------------------------------------------------------------------
-----------------------------------
Limit (cost=40928.89..40930.14 rows=500 width=159)
-> Sort (cost=40928.89..40954.76 rows=10346 width=159)
Sort Key: id
-> Bitmap Heap Scan on systemevents (cost=2898.06..40413.36 rows=1034
6 width=159)
Recheck Cond: (to_tsvector('english'::regconfig, message) @@ to_t
squery('131.211.112.9'::text))
-> Bitmap Index Scan on msgs_idx (cost=0.00..2895.47 rows=10346
width=0)
Index Cond: (to_tsvector('english'::regconfig, message) @@
to_tsquery('131.211.112.9'::text))
(7 rows)

works acceptable.

Stats:

syslog=# SELECT relname, relkind, reltuples, relpages
FROM pg_class WHERE relname LIKE 'systemevents%'; relname | relkind | reltuples | relpages
-------------------------------+---------+-------------+----------
systemevents_pkey | i | 2.06915e+06 | 71985
systemeventsproperties | r | 0 | 0
systemeventsproperties_pkey | i | 0 | 1
systemevents_id_seq | S | 1 | 1
systemeventsproperties_id_seq | S | 1 | 1
systemevents | r | 2.06915e+06 | 694826
(6 rows)

syslog=# SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'msg%';
relname | relkind | reltuples | relpages
----------+---------+-------------+----------
msgs_idx | i | 2.06915e+06 | 128069
(1 row)

How to use the right plan regardless of the 'LIMIT-size'?

Cheers,
--
Henk van Lingen, ICT-SC Netwerk & Telefonie, (o- -+
Universiteit Utrecht, Jenalaan 18a, room 0.12 /\ |
phone: +31-30-2538453 v_/_ |
http://henk.vanlingen.net/ http://www.tuxtown.net/netiquette/