Hello,

thanks for the report. Before I start digging, is it the NULL clause that makes the existing index index_reports_on_reported_at not effective?

Sorry I mean status field.

It looks like we need a composed index, we have the following indicies:

  add_index "reports", ["host_id"], :name => "index_reports_on_host_id" 
  add_index "reports", ["reported_at", "host_id"], :name => "index_reports_on_reported_at_and_host_id" 
  add_index "reports", ["reported_at"], :name => "index_reports_on_reported_at" 
  add_index "reports", ["status"], :name => "index_reports_on_status" 

Are you sure your recommendation create index concurrently on reports (reported_at DESC NULLS LAST) really solves the issue? I'd expect something like:

  add_index "reports", ["reported_at", "status"], :name => "index_reports_on_reported_at_and_status" 

For the record the relevant bit is this one:

def latest_events
  # 6 reports + header fits the events box nicely...
    Report.authorized(:view_reports).my_reports.interesting.search_for('reported > "7 days ago"').limit(6).includes(:host)
  end

And it's get more complicated for non-admin users where we need to join tables. For admin users the query is simple (but still problematic).

It looks like scoped_search adds the "NULLS LAST" statement which makes our index non-effective. We can workaround this in our codebase like this:

irb(main):008:0> Report.interesting.search_for('reported > "7 days ago"').limit(6).includes(:host)
  Report Load (0.5ms)  SELECT "reports".* FROM "reports" WHERE (status <> 0) AND (("reports"."reported_at" >= '2015-06-16')) ORDER BY "reports"."reported_at" DESC NULLS LAST LIMIT 6
=> []
irb(main):009:0> Report.interesting.where("reported_at > ?", 7.days.ago).limit(6).includes(:host)
  Report Load (0.7ms)  SELECT "reports".* FROM "reports" WHERE (status <> 0) AND (reported_at > '2015-06-15 09:14:05.855143') LIMIT 6
=> []

The problem can be with NULL records. I don't recall why we don`t run NOT NULL for reported_at column. The new API V2 requires this field to be present as well as our model (:presence => true, :uniqueness => {:scope => :host_id}). Therefore I think the proper way to fix this is to make the column a non-null one. That should fix the scoped_search query builder as well and no extra index is necessary.

Lukas Zapletal wrote:

The problem can be with NULL records. I don't recall why we don`t run NOT NULL for reported_at column. The new API V2 requires this field to be present as well as our model (:presence => true, :uniqueness => {:scope => :host_id}). Therefore I think the proper way to fix this is to make the column a non-null one. That should fix the scoped_search query builder as well and no extra index is necessary.

any idea how reported_at could be null? it was never designed to be null afaik.

Strange, even when I change reported_at to be non-null column, scoped search still adds "NULLS LAST" caluse:

irb(main):011:0> Report.interesting.search_for('reported > "7 days ago"').limit(6).includes(:host)
  Report Load (0.5ms)  SELECT "reports".* FROM "reports" WHERE (status <> 0) AND (("reports"."reported_at" >= '2015-06-16')) ORDER BY "reports"."reported_at" DESC NULLS LAST LIMIT 6
=> []
irb(main):012:0> Report.interesting.where("reported_at > ?", 7.days.ago).order("reported_at DESC").limit(6).includes(:host)
  Report Load (0.6ms)  SELECT "reports".* FROM "reports" WHERE (status <> 0) AND (reported_at > '2015-06-15 09:59:51.578137') ORDER BY reported_at DESC LIMIT 6
=> []

Hi,

I am the DB guy mentioned in the OP.
I just created the index this way to get a production system back into working order.

I do not think that you need:

  add_index "reports", ["reported_at", "status"], :name => "index_reports_on_reported_at_and_status" 

As you can see from the first explain sorting by reported_at triggers a full table scan which is expensive:

  Sort Key: reported_at
  -> Seq Scan on reports (cost=0.00..3247171.83 rows=1722055 width=538)

There is another index index_reports_on_reported_at on reported_at which can not be used by the query due to DESC being used together with NULLS LAST in the query.

The condition status <> 0 is already covered by the index index_reports_on_status.

The problem with degraded performance was gone right after the index was created.

Please let me know if you need any more information.

BTW: PostgreSQL 9.4.3 is used for this installation.

Lukas Zapletal wrote:

Strange, even when I change reported_at to be non-null column, scoped search still adds "NULLS LAST" caluse:

[...]

Is the code for scoped search checking columns for "not null" modifiers in the DB and generating different SQL statements based on that check?
If not then changing the column in the database will have no effect on the SQL generated by the application.

Hello,

I have created a patch that adds a migration to add "NOT NULL" on the created_at column. I believe PostgreSQL optimizer will then ignore "NULLS LAST" caluse and use the index. I am not sure tho if this needs reindexing after "NOT NULL" was added. I am currently lack of an instance with decent amount of reports.

Yeah I was hoping that optimizer will ignore "NULLS LAST" when the column is actually "NOT NULL" :-(

It seems the gem always adds the caluse: https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L517-L521 (it is hardcoded)

Ok I filed a patch against scoped_search not to add this clause when column is NOT NULL.

Until then we can modify our widget not to use scoped_search (my patch for review).

Lukas Zapletal wrote:

Hello,

I have created a patch that adds a migration to add "NOT NULL" on the created_at column. I believe PostgreSQL optimizer will then ignore "NULLS LAST" caluse and use the index. I am not sure tho if this needs reindexing after "NOT NULL" was added. I am currently lack of an instance with decent amount of reports.

I just did check that on a copy of my production DB.

The PostgreSQL optimizer does not optimize away the NULLS LAST in the SQL statement.

foreman_prod=# \d+ reports
                                                          Table "public.reports" 
   Column    |            Type             |                      Modifiers                       | Storage  | Stats target | Description 
-------------+-----------------------------+------------------------------------------------------+----------+--------------+-------------
 id          | integer                     | not null default nextval('reports_id_seq'::regclass) | plain    |              | 
 host_id     | integer                     | not null                                             | plain    |              | 
 reported_at | timestamp without time zone | not null                                             | plain    |              | 
 created_at  | timestamp without time zone | not null                                             | plain    |              | 
 updated_at  | timestamp without time zone | not null                                             | plain    |              | 
 status      | bigint                      |                                                      | plain    |              | 
 metrics     | text                        |                                                      | extended |              | 
Indexes:
    "reports_pkey" PRIMARY KEY, btree (id)
    "index_reports_on_host_id" btree (host_id)
    "index_reports_on_reported_at" btree (reported_at)
    "index_reports_on_reported_at_and_host_id" btree (reported_at, host_id)
    "index_reports_on_status" btree (status)
Foreign-key constraints:
    "reports_host_id_fk" FOREIGN KEY (host_id) REFERENCES hosts(id)

foreman_prod=# explain SELECT "reports".* FROM "reports" WHERE (status <> 0) AND (("reports"."reported_at" >= '2015-06-12')) ORDER BY "reports"."reported_at" DESC NULLS LAST LIMIT 6;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=328846.53..328846.54 rows=6 width=539)
   ->  Sort  (cost=328846.53..336132.00 rows=2914188 width=539)
         Sort Key: reported_at
         ->  Seq Scan on reports  (cost=0.00..276610.26 rows=2914188 width=539)
               Filter: ((status <> 0) AND (reported_at >= '2015-06-12 00:00:00'::timestamp without time zone))
(5 rows)

foreman_prod=# explain SELECT "reports".* FROM "reports" WHERE (status <> 0) AND (("reports"."reported_at" >= '2015-06-12')) ORDER BY "reports"."reported_at" DESC LIMIT 6;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..2.02 rows=6 width=539)
   ->  Index Scan Backward using index_reports_on_reported_at on reports  (cost=0.43..771574.14 rows=2914188 width=539)
         Index Cond: (reported_at >= '2015-06-12 00:00:00'::timestamp without time zone)
         Filter: (status <> 0)
(4 rows)

Since this behaviour of the optimizer in PostgreSQL is unexpected/surprising we are trying to find out if this is a bug in the optimizer or if this is a shortcoming that can not be addressed.

For completeness I have also checked if the creation of the combined index add_index "reports", ["reported_at", "status"], :name => "index_reports_on_reported_at_and_status" does help and can confirm that it does not:

foreman_prod=# create index on reports (status, reported_at);

foreman_prod=# \d+ reports
                                                          Table "public.reports" 
   Column    |            Type             |                      Modifiers                       | Storage  | Stats target | Description 
-------------+-----------------------------+------------------------------------------------------+----------+--------------+-------------
 id          | integer                     | not null default nextval('reports_id_seq'::regclass) | plain    |              | 
 host_id     | integer                     | not null                                             | plain    |              | 
 reported_at | timestamp without time zone | not null                                             | plain    |              | 
 created_at  | timestamp without time zone | not null                                             | plain    |              | 
 updated_at  | timestamp without time zone | not null                                             | plain    |              | 
 status      | bigint                      |                                                      | plain    |              | 
 metrics     | text                        |                                                      | extended |              | 
Indexes:
    "reports_pkey" PRIMARY KEY, btree (id)
    "index_reports_on_host_id" btree (host_id)
    "index_reports_on_reported_at" btree (reported_at)
    "index_reports_on_reported_at_and_host_id" btree (reported_at, host_id)
    "index_reports_on_status" btree (status)
    "reports_status_reported_at_idx" btree (status, reported_at)
Foreign-key constraints:
    "reports_host_id_fk" FOREIGN KEY (host_id) REFERENCES hosts(id)

foreman_prod=# explain analyse SELECT "reports".* FROM "reports" WHERE (status <> 0) AND (("reports"."reported_at" >= '2015-06-12')) ORDER BY "reports"."reported_at" DESC NULLS LAST LIMIT 6;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=328846.53..328846.54 rows=6 width=539) (actual time=1415.576..1415.579 rows=6 loops=1)
   ->  Sort  (cost=328846.53..336132.00 rows=2914188 width=539) (actual time=1415.574..1415.574 rows=6 loops=1)
         Sort Key: reported_at
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Seq Scan on reports  (cost=0.00..276610.26 rows=2914188 width=539) (actual time=0.007..855.953 rows=2926109 loops=1)
               Filter: ((status <> 0) AND (reported_at >= '2015-06-12 00:00:00'::timestamp without time zone))
               Rows Removed by Filter: 257108

Valid solutions to fix the problem:

• either remove NULLS LAST from the SQL query in the application code
• or create index concurrently on reports (reported_at DESC NULLS LAST);