CODEWITHSUNDEEP
sqldatabasepostgresqlpostgresql-9.1
AnApprentice
AnApprentice

Reputation: 110950

How to create a query that performs well and returns 10k records

I have the following query:

EXPLAIN ANALYZE SELECT "google_contacts".* 
FROM "google_contacts" 
WHERE "google_contacts"."user_id" = 1201

This query ends up taking a lot of time, below are the results:

Seq Scan on google_contacts  (cost=0.00..394.99 rows=9999 width=183)
    (actual time=0.008..2.038 rows=9999 loops=1)
      Filter: (user_id = 1201)
Total runtime: 2.552 ms

This table only has 10 columns and has an index on user_id... Any thoughts on why this is taking so long to run? Thanks

Upvotes: 0

Views: 115

Answers (2)

Kotu
Kotu

Reputation: 1102

If query returns too many rows, sequence scan will be more effective than using index. In this case You can improve performance using other methods. On the other hand, the bottleneck can be you network connection.

Upvotes: 0

MrTheWalrus
MrTheWalrus

Reputation: 9700

Am I missing something here? It looks as though the query takes ~2.5ms to run. I wouldn't characterize that as 'a lot of time'.

It is interesting that the query optimizer has chosen not to use your index on user_id (since the explain mentions doing a seq scan instead of an index scan), but my understanding is that it will choose whichever method it (the optimizer) thinks will run most efficiently, and that there's not a whole lot you can do about that.

Upvotes: 1

Related Questions