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