Hi group,
We have two related tables with event types and events. We query for a join between these two tables and experience that, when there is an to-be-expected very small result set, this query performs particularly poor. Understanding in this matter would be appreciated.
SELECT * from events_event_types WHERE id IN (71,999);
id | name | severity
----+------------------------+----------
71 | Xenteo Payment handled | 20
(1 row)
Following original query returns zero rows (as to be expected on what I showed above) and takes (relatively) a lot of time doing so:
SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=70 AND (eventType_id IN (71)) ORDER BY datetime DESC LIMIT 50;
id | carparkid | cleared | datetime | identity | generatedbystationid | eventtype_id | relatedstationid | processingstatus | id | name | severity
----+-----------+---------+----------+----------+----------------------+--------------+------------------+------------------+----+------+----------
(0 rows)
Time: 397.564 ms
Following query is much alike the original query, but I changed the "WHERE severity". It returns the number of rows are requested in LIMIT and takes only little time doing so:
SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=20 AND (eventType_id IN (71)) ORDER BY datetime DESC limit 50;
...
(50 rows)
Time: 1.604 ms
The latter much to prove that this is a problem related to small result sets.
Following query is much alike the original query, although I've added a dummy value (non-existent in event types table; "999") to the WHERE IN clause. It returns the same zero rows and takes only little time doing so:
SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=70 AND (eventType_id IN (71, 999)) ORDER BY datetime DESC LIMIT 50;
id | carparkid | cleared | datetime | identity | generatedbystationid | eventtype_id | relatedstationid | processingstatus | id | name | severity
----+-----------+---------+----------+----------+----------------------+--------------+------------------+------------------+----+------+----------
(0 rows)
Time: 1.340 ms
Now I have at least two possibilities:
- Implementing the dummy value as shown above in my source code to improve query performance (dirty but effective)
- Further investigating what is going on, which at this point is something I need help with
Thanks for your assistance in this matter!
Following are a number of details to describe the environment that this is seen in.
SELECT version();
PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
Postgres was installed as Debian package in Ubuntu 8.04 LTS.
SELECT count(*) FROM events_events;
7619991
SELECT count(*) FROM events_events WHERE eventtype_id=71;
50348
SELECT count(*) FROM events_event_types;
82
\d events_event_types
Table "public.events_event_types"
Column | Type | Modifiers
----------+------------------------+-----------------------------------------------------------------
id | bigint | not null default nextval('events_event_types_id_seq'::regclass)
name | character varying(255) | not null
severity | bigint | not null
Indexes:
"events_event_types_pkey" PRIMARY KEY, btree (id)
"events_event_types_name_key" UNIQUE, btree (name)
"events_event_types_severity_ind" btree (severity)
"test_events_eventtypes_id_severity_ind" btree (id, severity)
"test_events_eventtypes_severity_id_ind" btree (severity, id)
\d events_events
Table "public.events_events"
Column | Type | Modifiers
----------------------+--------------------------+------------------------------------------------------------
id | bigint | not null default nextval('events_events_id_seq'::regclass)
carparkid | bigint |
cleared | boolean | not null
datetime | timestamp with time zone |
identity | character varying(255) |
generatedbystationid | bigint |
eventtype_id | bigint | not null
relatedstationid | bigint |
processingstatus | character varying(255) | not null
Indexes:
"events_events_pkey" PRIMARY KEY, btree (id)
"events_events_cleared_ind" btree (cleared)
"events_events_datetime_eventtype_id_ind" btree (datetime, eventtype_id)
"events_events_datetime_ind" btree (datetime)
"events_events_eventtype_id_datetime_ind" btree (eventtype_id, datetime)
"events_events_eventtype_id_ind" btree (eventtype_id)
"events_events_identity_ind" btree (identity)
"events_events_not_cleared_ind" btree (cleared) WHERE NOT cleared
"events_events_processingstatus_new" btree (processingstatus) WHERE processingstatus::text = 'NEW'::text
"test2_events_events_eventtype_id_severity_ind" btree (datetime, eventtype_id, cleared)
"test3_events_events_eventtype_id_severity_ind" btree (cleared, datetime, eventtype_id)
"test4_events_events_eventtype_id_severity_ind" btree (datetime, cleared, eventtype_id)
"test5_events_events_eventtype_id_severity_ind" btree (datetime, cleared)
"test_events_events_eventtype_id_severity_ind" btree (eventtype_id, cleared)
Foreign-key constraints:
"fk88fe3effa0559276" FOREIGN KEY (eventtype_id) REFERENCES events_event_types(id)
Groeten, best regards,
Sander Verhagen
