Hello all,
In continuing my quest from yesterday to speed up some INSERTs I'm doing, I
came across a SELECT statement in my trigger that is taking .433 seconds to
run. Here is a sample query:
select * from status s where s.site_id = 18 and s.host_id = 49 and
s.product = 'BETA' and s.class = 'APPS' and s.subclass = 'MONITOR' ;
I had drastically increased performance of some other queries this morning
by adding a multicolumn index on the table, so I thought I would try here
as well. Here's the table and the index I created:
Table "status"
Attribute | Type | Modifier
-----------+-----------+----------
site_id | bigint | not null
host_id | bigint | not null
product | varchar() | not null
class | varchar() | not null
subclass | varchar() | not null
status | varchar() | not null
msg | varchar() |
tstamp | timestamp |
Indices: status_5_column_index,
status_host_id_key,
status_site_id_key
The query I used to create the index: create index status_5_column_index
on status (site_id, host_id, product, class, subclass);
I then ran some tests. It's still taking .433 seconds on average. Is a 5
column multicolumn index too much for postgres to handle? Is my query not
using the index at all? Is my database designed horrendously and the mere
fact that I have a select with 5 where conditions making you ill? :-) It
worked for two columns this morning, so I thought I'd give this a shot.
Thanks,
Fran
