Re: surprising query optimisation

On 28/11/2018 22:49, Stephen Frost wrote:
> * Chris Withers (chris@withers.org) wrote:
>> We have an app that deals with a lot of queries, and we've been slowly
>> seeing performance issues emerge. We take a lot of free form queries from
>> users and stumbled upon a very surprising optimisation.
>>
>> So, we have a 'state' column which is a 3 character string column with an
>> index on it. Despite being a string, this column is only used to store one
>> of three values: 'NEW', 'ACK', or 'RSV'.
> 
> Sounds like a horrible field to have an index on.

That's counter-intuitive for me. What leads you to say this and what 
would you do/recommend instead?

> Really though, if you want something more than wild speculation, posting
> the 'explain analyze' of each query along with the actual table
> definitions and sizes and such would be the best way to get it.

table definition:

# \d alerts_alert
               Table "public.alerts_alert"
      Column      |           Type           | Modifiers
-----------------+--------------------------+-----------
  tags            | jsonb                    | not null
  id              | character varying(86)    | not null
  earliest_seen   | timestamp with time zone | not null
  latest_seen     | timestamp with time zone | not null
  created         | timestamp with time zone | not null
  modified        | timestamp with time zone | not null
  type            | character varying(300)   | not null
  state           | character varying(3)     | not null
  until           | timestamp with time zone |
  latest_note     | text                     | not null
  created_by_id   | integer                  | not null
  modified_by_id  | integer                  | not null
  owner_id        | integer                  |
  owning_group_id | integer                  | not null
  latest_new      | timestamp with time zone | not null
Indexes:
     "alerts_alert_pkey" PRIMARY KEY, btree (id)
     "alert_tags_index" gin (tags)
     "alerts_alert_1efacf1d" btree (latest_seen)
     "alerts_alert_3103a7d8" btree (until)
     "alerts_alert_599dcce2" btree (type)
     "alerts_alert_5e7b1936" btree (owner_id)
     "alerts_alert_9ae73c65" btree (modified)
     "alerts_alert_9ed39e2e" btree (state)
     "alerts_alert_b3da0983" btree (modified_by_id)
     "alerts_alert_c5151f5a" btree (earliest_seen)
     "alerts_alert_e2fa5388" btree (created)
     "alerts_alert_e93cb7eb" btree (created_by_id)
     "alerts_alert_efea2d76" btree (owning_group_id)
     "alerts_alert_id_13155e16_like" btree (id varchar_pattern_ops)
     "alerts_alert_latest_new_e8d1fbde_uniq" btree (latest_new)
     "alerts_alert_state_90ab480b_like" btree (state varchar_pattern_ops)
     "alerts_alert_type_3021f46f_like" btree (type varchar_pattern_ops)
Foreign-key constraints:
     "alerts_alert_created_by_id_520608c0_fk_alerts_user_id" FOREIGN KEY 
(created_by_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY DEFERRED
     "alerts_alert_modified_by_id_6db4b04b_fk_alerts_user_id" FOREIGN 
KEY (modified_by_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY 
DEFERRED
     "alerts_alert_owner_id_0c00548a_fk_alerts_user_id" FOREIGN KEY 
(owner_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY DEFERRED
     "alerts_alert_owning_group_id_a4869b66_fk_auth_group_id" FOREIGN 
KEY (owning_group_id) REFERENCES auth_group(id) DEFERRABLE INITIALLY 
DEFERRED
Referenced by:
     TABLE "alerts_alertevent" CONSTRAINT 
"alerts_alertevent_alert_id_edd734b8_fk_alerts_alert_id" FOREIGN KEY 
(alert_id) REFERENCES alerts_alert(id) DEFERRABLE INITIALLY DEFERRED

Row counts by state:

# select state, count(*) from alerts_alert group by 1 order by 1;
  state |  count
-------+---------
  ACK   |    1053
  NEW   |    1958
  RSV   | 1528623
(3 rows)

here's an example of the "bad" query plan:
https://explain.depesz.com/s/cDkp

here's an example with all the "state!='RSV'" clauses rewritten as I 
described:
https://explain.depesz.com/s/B9Xi

> I'd suggest you check out the wiki article written about this kind of
> question:
> 
> https://wiki.postgresql.org/wiki/Slow_Query_Questions

Thanks!

Chris