Improve PostgreSQL query analyser

[fabioperrella]

It's very hard to figure out if a table needs a new index looking at the result of a PostgreSQL EXPLAIN query.

PostgreSQL's EXPLAIN result is not deterministic and varies depending on some internal state in the database statistics.

For example, I run a simple explain in a table which has a proper index, in 2 different moments, and got 2 different results:

[1] pry(main)> Feature.where(plan_id: 312312).explain
  Feature Load (4.0ms)  SELECT "features".* FROM "features" WHERE "features"."plan_id" = $1  [["plan_id", 312312]]
=> EXPLAIN for: SELECT "features".* FROM "features" WHERE "features"."plan_id" = $1 [["plan_id", 312312]]
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on features  (cost=0.00..1.06 rows=1 width=72)
   Filter: (plan_id = 312312)
(2 rows)

[2] pry(main)> Feature.count
   (2.8ms)  SELECT COUNT(*) FROM "features"
=> 5
[3] pry(main)> Plan.count
   (2.7ms)  SELECT COUNT(*) FROM "plans"
=> 2

###################################################################################################################

[1] pry(main)> Feature.where(plan_id: 312312).explain
  Feature Load (2.3ms)  SELECT "features".* FROM "features" WHERE "features"."plan_id" = $1  [["plan_id", 312312]]
=> EXPLAIN for: SELECT "features".* FROM "features" WHERE "features"."plan_id" = $1 [["plan_id", 312312]]
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Bitmap Heap Scan on features  (cost=4.18..12.64 rows=4 width=72)
   Recheck Cond: (plan_id = 312312)
   ->  Bitmap Index Scan on index_features_on_plan_id  (cost=0.00..4.18 rows=4 width=0)
         Index Cond: (plan_id = 312312)

So, I don't know how to be certain if an index is missing or not.

Maybe an alternative would be parsing the query to extract the filter conditions, describe the table to list the current indexes and compare if they match, but I would do it only as the last alternative because:

• it's hard to parse a query and extract the filters
• it would implement another kind of explain query