Greetings, On Fri, Nov 30, 2018 at 07:52 Chris Withers <ch...@withers.org> wrote:
> On 28/11/2018 22:49, Stephen Frost wrote: > > * Chris Withers (ch...@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 Have you tried a partial index on state!=‘RSV’? Thanks, Stephen > <https://wiki.postgresql.org/wiki/Slow_Query_Questions> >
