CODEWITHSUNDEEP
sqlpostgresql
Fabien Henon
Fabien Henon

Reputation: 823

Query very long to execute

I am executing a Postgresql database on a 2GB RAM VPS.

The settings are :

max_connections = 100
work_mem=1MB
shared_buffers=128MB

I am executing a pretty simple query with a million rows :

SELECT s.executionTime, g.date, s.name
FROM SimulationStatsGroup g
LEFT JOIN SimulationStats s ON s.group_id = g.id
WHERE g.name = 'general'
ORDER BY g.date DESC

I have 2 tables : SimulationStatsGroup and SimulationStats. SimulationStatsGroup contains between 1 to 13 SimulationStats. SimulationStats is a simple entity that contains numeric values like executionTime used by my application. Each SimulationStatsGroup and SimulationStats have a name.

Here is the EXPLAIN ANALYZE that I get : http://explain.depesz.com/s/auLK

Why is my query so long to execute ?

Upvotes: 0

Views: 99

Answers (2)

Jayadevan
Jayadevan

Reputation: 1342

Create indexes on SimulationStats(group_id) and SimulationStatsGroup(id).

Upvotes: 1

Mike Jones
Mike Jones

Reputation: 532

In the Sort (step #2) in the explain plan, it looks like the database is either lugging around unreferenced columns (not optimum) and/or sorting by them (ouch). Honestly though, I don't work on Postgres, so that is just an educated guess. The database engine may not be smart enough to discard unreferenced columns early in the process. I'd try this SQL to nudge the database engine into discarding unreferenced columns before it gets to the sort, and you may see a significant runtime improvement:

SELECT s.executionTime, g.date, s.name
FROM ( select id, date from SimulationStatsGroup WHERE g.name = 'general') as g
LEFT JOIN ( select s.group_id, s.name, s.executionTime from SimulationStats ) as s 
       ON s.group_id = g.id   
ORDER BY g.date DESC

If this version shows a runtime improvement, please run another Explain, and let us know if there are less columns list in the Sort step. If so, my hunch was likely correct. If correct, hopefully the Postgres developers will take note and try to discard unreferenced columns for us in a future version, instead of us manually coding it.

Upvotes: 0

Related Questions