CODEWITHSUNDEEP
pythonperformancepostgresql
user6611771
user6611771

Reputation:

How to improve query performance to select info from postgress?

I have a flask app:

db = SQLAlchemy(app)
@app.route('/')
def home():
    query = "SELECT door_id FROM table WHERE id = 2422628557;"
    result = db.session.execute(query)
    return json.dumps([dict(r) for r in result])

When I execute: curl http://127.0.0.1:5000/

I got result very quickly [{"door_id": 2063805}]

But when I reverse my query: query = "SELECT id FROM table WHERE door_id = 2063805;" everything work very-very slow.

Probably I have index on id attribute and don't have such on door_id. How can I improve performance? How to add index on door_id?

Upvotes: 0

Views: 65

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51446

If you want index on that column, just create it:

create index i1 on table (door_id)

Then depending on your settings you might have to analyze it to introduce to query planner, eg:

analyze table;

keep in mind - all indexes require additional IO on data manipulation

Upvotes: 1

Gurmokh
Gurmokh

Reputation: 2091

Look at the explain plan for your query. 

EXPLAIN 
"SELECT door_id FROM table WHERE id = 2422628557;"

It is likely you are seeing something like this: 

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on table (cost=0.00..483.00 rows=99999 width=244)
   Filter: (id = 2422628557)

The Seq Scan is checking every single row in this table and this is then being filtered by the id you are restricting by.

What you should do in this occasion is to add an index to the ID column. The plan will change to something like: 

                                QUERY PLAN
 -----------------------------------------------------------------------------
 Index Scan using [INDEX_NAME] on table  (cost=0.00..8.27 rows=1 width=244)
   Index Cond: (id = 2422628557)

The optimiser will be using the index to reduce the row look ups for your query.

This will speed up your query.

Upvotes: 0

Related Questions