Postgres uses wrong index
indexing, postgresql, postgresql-performance, sql-execution-plan
Solution
Very common values in the predicates for `ssid_id` and `ap_id` can make it cheaper for Postgres to pick the smaller index `session_lim_values_idx` (only 1 `date` column) over the seemingly better fit, but bigger index `session_date_ssid_apid_idx` (4 columns) and filter the rest.
In your case around 4 % of the rows have `ssid_id=42 AND ap_id=1731`. That shouldn't normally warrant the switch to the smaller index. But several other factors are in play that may tilt the scale, basically cost settings and statistics. Details:
- Keep PostgreSQL from sometimes choosing a bad query plan
What to do?
Adjust your cost settings if you did not do so already as advised in linked the answer above.
Increase the statistics target for involved columns `ssid_id`, `ap_id` and run `ANALYZE`:
- Check statistics targets in PostgreSQL
One special factor here: Postgres collects separate statistics for expressions in indexes. Check with:
SELECT * FROM pg_statistic
WHERE starelid = 'session_date_ssid_apid_idx'::regclass;
You'll find a dedicated row for the expression `date(associationtime)`. More details:
- Index that is not used, yet influences query
Make the index `session_date_ssid_apid_idx` more attractive (smaller) by removing the 4th column `"date_trunc('hour'::text, associationtime)`. Looking at your later added table definition, you already did that.
I would rather use the standard syntax for casts: `cast(associationtime AS date)` instead of the function syntax `date(associationtime)`. Not saying that matters at all, I just know the standard way to work properly. You can use the shorthand syntax `associationtime::date` in your queries, that's compatible with the expression index, but use the verbose form in the index definition.
Also, test with `EXPLAIN ANALYZE` which query plan is actually faster by removing / recreating only the index you want to test. Then you'll see whether Postgres picked the best plan after all.
You have quite a number of indexes, I would check whether all of them are actually used and get rid of the rest. Indexes have maintenance cost and it's generally beneficial to concentrate on fewer indexes if possible (fit in cache more easily and may be cached already when needed). Weigh cost vs. benefit.
Aside
I'd use:
SUM(extract(epoch FROM disassociationtime
- associationtime)::int) AS sessionduration
Problem
I've got a query: ``` EXPLAIN ANALYZE SELECT CAST(DATE(associationtime) AS text) AS date , cast(SUM(extract(epoch FROM disassociationtime) - extract(epoch FROM associationtime)) AS bigint) AS sessionduration, cast(SUM(tx) AS bigint)AS tx, cast(SUM(rx) AS bigint) AS rx, cast(SUM(dataRetries) AS bigint) AS DATA, cast(SUM(rtsRetries) AS bigint) AS rts, count(*) FROM SESSION WHERE ssid_id=42 AND ap_id=1731 AND DATE(associationtime)>=DATE('Tue Nov 04 00:00:00 MSK 2014') AND DATE(associationtime)<=DATE('Thu Nov 20 00:00:00 MSK 2014') GROUP BY(DATE(associationtime)) ORDER BY DATE(associationtime); ``` The output is: ``` GroupAggregate (cost=0.44..17710.66 rows=1 width=32) (actual time=4.501..78.880 rows=17 loops=1) -> Index Scan using session_lim_values_idx on session (cost=0.44..17538.94 rows=6868 width=32) (actual time=0.074..73.266 rows=7869 loops=1) Index Cond: ((date(associationtime) >= '2014-11-04'::date) AND (date(associationtime) <= '2014-11-20'::date)) Filter: ((ssid_id = 42) AND (ap_id = 1731)) Rows Removed by Filter: 297425 Total runtime: 78.932 ms ``` Look at this line: ``` Index Scan using session_lim_values_idx ``` As you can see, query uses three fields to scan: ssid_id, ap_id and associationtime. I've got an index fo this: ``` ssid_pkey | btree | {id} ap_pkey | btree | {id} testingshit_pkey | btree | {one,two,three} session_date_ssid_idx | btree | {ssid_id,date(associationtime),"date_trunc('hour'::text, associationtime)"} session_pkey | btree | {associationtime,disassociationtime,sessionduration,clientip,clientmac,devicename,tx,rx,protocol,snr,rssi,dataretries,rtsretries } session_main_idx | btree | {ssid_id,ap_id,associationtime,disassociationtime,sessionduration,clientip,clientmac,devicename,tx,rx,protocol,snr,rssi,dataretres,rtsretries} session_date_idx | btree | {date(associationtime),"date_trunc('hour'::text, associationtime)"} session_date_apid_idx | btree | {ap_id,date(associationtime),"date_trunc('hour'::text, associationtime)"} session_date_ssid_apid_idx | btree | {ssid_id,ap_id,date(associationtime),"date_trunc('hour'::text, associationtime)"} ap_apname_idx | btree | {apname} users_pkey | btree | {username} user_roles_pkey | btree | {user_role_id} session_lim_values_idx | btree | {date(associationtime)} ``` It's called `session_date_ssid_apid_idx`. But why query uses wrong index? session_date_ssid_apid_idx: ``` ------------+-----------------------------+------------------------------------------- ssid_id | integer | ssid_id ap_id | integer | ap_id date | date | date(associationtime) date_trunc | timestamp without time zone | date_trunc('hour'::text, associationtime) ``` session_lim_values_idx: ``` date | date | date(associationtime) ``` What index would you create? UPD: `\d session` ``` --------------------+-----------------------------+------------------------------------------------------ id | integer | NOT NULL DEFAULT nextval('session_id_seq'::regclass) ssid_id | integer | NOT NULL ap_id | integer | NOT NULL associationtime | timestamp without time zone | NOT NULL disassociationtime | timestamp without time zone | NOT NULL sessionduration | character varying(100) | NOT NULL clientip | character varying(100) | NOT NULL clientmac | character varying(100) | NOT NULL devicename | character varying(100) | NOT NULL tx | integer | NOT NULL rx | integer | NOT NULL protocol | character varying(100) | NOT NULL snr | integer | NOT NULL rssi | integer | NOT NULL dataretries | integer | NOT NULL rtsretries | integer | NOT NULL ╚эфхъё√: "session_pkey" PRIMARY KEY, btree (associationtime, disassociationtime, sessionduration, clientip, clientmac, devicename, tx, rx, protocol, snr, rssi, dataretries, rtsretries) "session_date_ap_ssid_idx" btree (ssid_id, ap_id, associationtime) "session_date_apid_idx" btree (ap_id, date(associationtime), date_trunc('hour'::text, associationtime)) "session_date_idx" btree (date(associationtime), date_trunc('hour'::text, associationtime)) "session_date_ssid_apid_idx" btree (ssid_id, ap_id, associationtime) "session_date_ssid_idx" btree (ssid_id, date(associationtime), date_trunc('hour'::text, associationtime)) "session_lim_values_idx" btree (date(associationtime)) "session_main_idx" btree (ssid_id, ap_id, associationtime, disassociationtime, sessionduration, clientip, clientmac, devicename, tx, rx, protocol, snr, rssi, dataretries, rtsretries) ```