Speed up database query by removing unused ordering

Description

Note that I'm using Postgresql-specific data to describe this issue, but the improvement impacts most (if not all) DBMSses.

Our database is configured to log queries that take a long time to execute. Browsing these logs, this query pops up frequently:

Typical durations range from 200 to 3000 milliseconds.

The most obvious fix to me (adding an index on the column "transportType") is already in place.

I performed this query on an old dump of our database:

After running ANALYZE, this is what EXPLAIN reports:

"Sort (cost=13327.88..13514.34 rows=74584 width=32)"
" Sort Key: jid"
" -> Bitmap Heap Scan on gatewayregistration (cost=516.04..5828.34 rows=74584 width=32)"
" Recheck Cond: ((transporttype)::text = 'live'::text)"
" -> Bitmap Index Scan on gatewayreg_type_idx (cost=0.00..516.04 rows=74584 width=0)"
" Index Cond: ((transporttype)::text = 'live'::text)"

The query takes 1904ms to execute on my computer.

Next, I changed the query to exclude the 'ORDER BY' clause:

Now, EXPLAIN reports:

"Bitmap Heap Scan on gatewayregistration (cost=516.04..5828.34 rows=74584 width=8)"
" Recheck Cond: ((transporttype)::text = 'live'::text)"
" -> Bitmap Index Scan on gatewayreg_type_idx (cost=0.00..516.04 rows=74584 width=0)"
" Index Cond: ((transporttype)::text = 'live'::text)"

The query takes 1478ms now.

Apart from the drop in execution time (~400ms) There seems to be quite a substantial drop in costs as well: 13327.88..13514.34 versus 516.04..5828.34

Getting the results ordered doesn't make sense to me when looking at the code. The result that is fabricated based on the database result is an unordered collection anyway. I would suggest to drop the 'ORDER BY' clause from the query, as it doesn't seem to serve a function, but does cause significant overhead.

Environment

None
Fixed
Your pinned fields
Click on the next to a field label to start pinning.

Assignee

Daniel Henninger

Reporter

Guus der Kinderen