Strange workaround for slow query - Mailing list pgsql-performance

----------
 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)

Can someone explain this behaviour?

Thanks in advance!

Best regards,