Rails generated model with migration never uses index when doing a where

Question

I created a model with rails g model project name # with more columns

Whenever I run explain on the sql query postgres never uses the index. Why is that?

my_db=# \d projects
                                         Table "public.projects"
   Column    |            Type             | Collation | Nullable |               Default
-------------+-----------------------------+-----------+----------+--------------------------------------
 id          | integer                     |           | not null | nextval('projects_id_seq'::regclass)
 name        | character varying           |           |          |
 active      | boolean                     |           | not null | true
 team_id     | integer                     |           |          |
 created_at  | timestamp without time zone |           | not null |
 updated_at  | timestamp without time zone |           | not null |
 account_id  | integer                     |           |          |
Indexes:
    "projects_pkey" PRIMARY KEY, btree (id)
    "index_projects_on_account_id" btree (account_id)
    "index_projects_on_team_id" btree (team_id)
Foreign-key constraints:
    "fk_rails_b4884d7210" FOREIGN KEY (account_id) REFERENCES accounts(id)
    "fk_rails_ecc227a0c2" FOREIGN KEY (team_id) REFERENCES teams(id)
Referenced by:
    TABLE "products" CONSTRAINT "fk_rails_7327dd6d39" FOREIGN KEY (project_id) REFERENCES projects(id)

And when I search I get this:

my_db=# explain select name from projects where id = 28;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on projects  (cost=0.00..2.60 rows=1 width=32)
   Filter: (id = 28)
(2 rows)

I ask ActiveRecord about the index and get false:

ActiveRecord::Base.connection.index_exists?(:projects, :id)
=> false

projects_pkey is right there under Indexes and yet Postgres is using a Seq Scan on projects. I have tried to change the query search for ids greater than 100000 or less than 100000. It doesn't matter, it's always using a Seq Scan

Can someone explain why it isn't using the index? On a small dataset this is fine. However the question came about from running a query that joins many tables and running explain on each of those still uses a Seq Scan which got me asking. (Regarding the query, it is joining about 10 tables with a few through tables. The dataset is not large, just wide)

Hope this will help - https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/ — Aarthi, Aug 08 '19 at 05:10
To build on what @Rthi said, heres a question about this with come good answers https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index. Basically, they're saying that forcing the db to use an index can cause problems down the road and was consciously left out of postres and that postres' optimizer is pretty good at detecting when an index is needed — Int'l Man Of Coding Mystery, Aug 08 '19 at 08:09
Possible duplicate of [Selecting primary key:Why postgres prefers to do sequential scan vs index scan](https://stackoverflow.com/questions/43639877/selecting-primary-keywhy-postgres-prefers-to-do-sequential-scan-vs-index-scan) — Lyzard Kyng, Aug 08 '19 at 15:38

michristofides · Accepted Answer · 2019-08-08T10:43:52.430

The link in @Rthi's comment gives some good advice!

In short, Postgres is not using the index either because:

It can't
It doesn't think it would be faster

1 is unlikely but could be the case if for some reason the index no longer exists.

2 is more likely. Sequential scans are faster for small tables! As such it may be that the Postgres statistics are wrong. If so you need to do an ANALYZE (or VACUUM ANALYZE) to get it up to date: https://www.postgresql.org/docs/current/sql-analyze.html

If you are running these on a development database with less data, that would also explain its choice!

For more info, you could run EXPLAIN (ANALYZE, BUFFERS). There are several tools to make viewing and sharing these easier, listed here: https://wiki.postgresql.org/wiki/Performance_Analysis_Tools#Web_tools

Disclaimer: I work on the most recent one listed, pgMustard.

Rails generated model with migration never uses index when doing a where

1 Answers1