CODEWITHSUNDEEP
sqlpostgresql
Tony
Tony

Reputation: 3805

How to improve this Postgres query?

I've got the sql query:

SELECT cast(AVG(SNR) AS integer) AS snr,
       cast(AVG(RSSI) AS integer) AS rts
FROM SESSION
WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');

It works slow, because 21 days contains 300k rows.

 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=346.794..346.795 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.014..282.512 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 346.867 ms

How can I improve my query? May I create index or something?

UPD:

Index on associationtime doesn't help.

postgres=# CREATE INDEX session_lim_values_idx ON session (associationtime);
CREATE INDEX
postgres=# EXPLAIN (ANALYZE) SELECT cast(AVG(SNR) as integer) as snr, cast(AVG(RSSI) as integer)  as rts FROM session WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=347.654..347.654 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.014..283.344 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 347.731 ms

And DATE(associationtime), too:

postgres=# CREATE INDEX session_lim_values_idx ON session (DATE(associationtime));
CREATE INDEX
postgres=# EXPLAIN (ANALYZE) SELECT cast(AVG(SNR) as integer) as snr, cast(AVG(RSSI) as integer)  as rts FROM session WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=341.050..341.050 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.015..278.247 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 341.129 ms

Upvotes: 0

Views: 100

Answers (3)

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

As you only care about whole days you may want to cache the result in a materialized view.

CREATE MATERIALIZED VIEW matview_avg_session
AS SELECT cast(AVG(SNR) AS integer) AS snr,
   cast(AVG(RSSI) AS integer) AS rts
   FROM SESSION
   WHERE DATE(associationtime) > DATE(NOW()- INTERVAL '21 DAYS');

then access the data like this:

SELECT * FROM matview_avg_session;

and refresh it (automatically once per day) like this:

REFRESH MATERIALIZED VIEW matview_avg_session;

or you look at this answer on how to create a trigger for refreshing it, but keep in mind that you don't want to do this after EVERY insert... Refresh a materialized view automatically using a rule or notify

Upvotes: 3

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

remove the date conversion from associationtime so the index can catch on.

SELECT cast(AVG(SNR) AS integer) AS snr,
       cast(AVG(RSSI) AS integer) AS rts
FROM SESSION
WHERE associationtime > DATE(NOW() - INTERVAL '20 DAYS');

If that doesn't help do a VACUUM ANALYZE on it, then try again.

Upvotes: 1

paubo147
paubo147

Reputation: 778

You could use a covering index, i.e., to force the executor using a index-only scan.

For covering index you add columns used in where clauses first then columns used in group by, then columns used in order by and then columns used in select.

  ALTER TABLE session ADD KEY ix1(date(associationtime), <remaining_columns>);

where <remaining_columns> are those, you would write in the group by clause in the SFW statement.

Upvotes: 1

Related Questions