Re: surprising query optimisation

Greetings,

On Fri, Nov 30, 2018 at 07:52 Chris Withers <chris(at)withers(dot)org> wrote:

> On 28/11/2018 22:49, Stephen Frost wrote:
> > * Chris Withers (chris(at)withers(dot)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

Have you tried a partial index on state!=‘RSV’?

Thanks,

Stephen

> <https://wiki.postgresql.org/wiki/Slow_Query_Questions>
>