-
Incident report
-
Resolution: Fixed
-
Trivial
-
2.0.8
-
PostgreSQL 8 and 9
The page tr_events.php takes mighty long to load. The responsible query is:
SELECT e.* FROM events e
WHERE e.objectid='35845' AND e.objectid BETWEEN 000000000000000
AND 099999999999999 AND e.eventid<=20907683 ORDER BY e.eventid
DESC LIMIT 20 OFFSET 0
make_small_eventlist() -> CAPIObject->get() -> CAPIObject->__call() -> czbxrpc::call() -> czbxrpc::callAPI() -> call_user_func() -> CEvent->get() -> DBselect()
My table has about 19 million rows. Even after being cached, the result is bad. The first attempt took more than 6 minutes. Further attempts are still really slow:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..74786.96 rows=20 width=44) (actual time=0.116..160550.281 rows=14 loops=1)
-> Index Scan Backward using events_pkey on events e (cost=0.00..2763378.33 rows=739 width=44) (actual time=0.115..160550.274 rows=14 loops=1)
Index Cond: (eventid <= 20907683)
Filter: ((objectid >= 0) AND (objectid <= 99999999999999::bigint) AND (objectid = 35845::bigint))
Rows Removed by Filter: 19551829
Total runtime: 160550.323 ms
(6 rows)
Kurz darauf:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..74786.87 rows=20 width=44) (actual time=0.063..9047.472 rows=14 loops=1)
-> Index Scan Backward using events_pkey on events e (cost=0.00..2763374.88 rows=739 width=44) (actual time=0.062..9047.467 rows=14 loops=1)
Index Cond: (eventid <= 20907683)
Filter: ((objectid >= 0) AND (objectid <= 99999999999999::bigint) AND (objectid = 35845::bigint))
Rows Removed by Filter: 19551829
Total runtime: 9047.504 ms
(6 rows)
As you can see, the existing index is used, but it seems to be very inefficient. http://www.postgresql.org/docs/9.1/static/indexes-multicolumn.html indicates that order matters when it comes to multi-column indexes. Consequently I attempted to create an additional index (objectid,eventid,object) just to try the different order. PG decided to use my index on the next explain analyze and the results were vastly better. The following output if from a different database where results are not that bad, but using the new index is 7000 times quicker:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..28639.83 rows=20 width=44) (actual time=5.558..7277.209 rows=6 loops=1)
-> Index Scan Backward using events_pkey on events e (cost=0.00..943682.37 rows=659 width=44) (actual time=5.558..7277.207 rows=6 loops=1)
Index Cond: (eventid <= 20907683)
Filter: ((objectid >= 0) AND (objectid <= 99999999999999::bigint) AND (objectid = 35508::bigint))
Total runtime: 7277.249 ms
(5 rows)
create index events_different_order on events (objectid,eventid,object);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..80.59 rows=20 width=44) (actual time=0.045..0.062 rows=6 loops=1)
-> Index Scan Backward using events_different_order on events e (cost=0.00..2655.36 rows=659 width=44) (actual time=0.045..0.061 rows=6 loops=1)
Index Cond: ((objectid >= 0) AND (objectid <= 99999999999999::bigint) AND (objectid = 35508::bigint) AND (eventid <= 20907683))
Total runtime: 0.104 ms
(4 rows)
I'm not sure if the issue exists for Trunk too. The index is different now (source, object, objectid, eventid). I'd expect it to worsen the case, if the query is the same.