Re: less than 2 sec for response - possible?

On 02.07.2016 02:54, trafdev wrote:
> Hi.
>
> I'm trying to build an OLAP-oriented DB based on PostgresSQL.
>
> User works with a paginated report in the web-browser. Interface allows
> to fetch data for a custom date-range selection,
> display individual rows (20-50 per page) and totals (for entire
> selection, even not visible on the current page) and sorting by any column.
>
> The main goal is to deliver results of the basic SELECT queries to the
> end-user in less than 2 seconds.
>
> I was able to achieve that except for one table (the biggest one).
>
> It consist of multiple dimensions (date, gran, aid, pid, sid, fid,
> subid) and metrics (see below).
> User can filter by any dimension and sort by any metric.
>
> Here is a CREATE script for this table:
>
> CREATE TABLE stats.feed_sub
> (
> date date NOT NULL,
> gran interval NOT NULL,
> aid smallint NOT NULL,
> pid smallint NOT NULL,
> sid smallint NOT NULL,
> fid smallint NOT NULL,
> subid text NOT NULL,
> rev_est_pub real NOT NULL,
> rev_est_feed real NOT NULL,
> rev_raw real NOT NULL,
> c_total bigint NOT NULL,
> c_passed bigint NOT NULL,
> q_total bigint NOT NULL,
> q_passed bigint NOT NULL,
> q_filt_geo bigint NOT NULL,
> q_filt_browser bigint NOT NULL,
> q_filt_os bigint NOT NULL,
> q_filt_ip bigint NOT NULL,
> q_filt_subid bigint NOT NULL,
> q_filt_pause bigint NOT NULL,
> q_filt_click_cap_ip bigint NOT NULL,
> q_filt_query_cap bigint NOT NULL,
> q_filt_click_cap bigint NOT NULL,
> q_filt_rev_cap bigint NOT NULL,
> q_filt_erpm_floor bigint NOT NULL,
> c_filt_click_cap_ip bigint NOT NULL,
> c_filt_doubleclick bigint NOT NULL,
> c_filt_url_expired bigint NOT NULL,
> c_filt_fast_click bigint NOT NULL,
> c_filt_delay_clicks bigint NOT NULL,
> c_filt_ip_mismatch bigint NOT NULL,
> c_filt_ref_mismatch bigint NOT NULL,
> c_filt_lng_mismatch bigint NOT NULL,
> c_filt_ua_mismatch bigint NOT NULL,
> res_impr bigint NOT NULL,
> rev_ver_pub real,
> rev_ver_feed real,
> c_ver bigint,
> q_filt_ref bigint NOT NULL
> )
> WITH (
> OIDS=FALSE
> );
>
> CREATE INDEX ix_feed_sub_date
> ON stats.feed_sub
> USING brin
> (date);
>
> CREATE UNIQUE INDEX ixu_feed_sub
> ON stats.feed_sub
> USING btree
> (date, gran, aid, pid, sid, fid, subid COLLATE pg_catalog."default");
>
> Here is some sizing info (https://wiki.postgresql.org/wiki/Disk_Usage):
>
> relation,size
> stats.feed_sub,5644 MB
> stats.ixu_feed_sub,1594 MB
>
> row_estimate
> 15865627
>
> Here is the typical query (for totals beige):
> SELECT
> sum(stats.feed_sub.c_filt_click_cap_ip) AS clicks_from_ip,
> sum(stats.feed_sub.c_filt_doubleclick) AS clicks_on_target,
> sum(stats.feed_sub.c_filt_delay_clicks) AS ip_click_period,
> sum(stats.feed_sub.c_filt_fast_click) AS fast_click,
> sum(stats.feed_sub.c_filt_ip_mismatch) AS ip_mismatch,
> sum(stats.feed_sub.c_filt_lng_mismatch) AS lng_mismatch,
> sum(stats.feed_sub.c_filt_ref_mismatch) AS ref_mismatch,
> sum(stats.feed_sub.c_filt_ua_mismatch) AS ua_mismatch,
> sum(stats.feed_sub.c_filt_url_expired) AS url_expired,
> stats.feed_sub.subid AS stats_feed_sub_subid,
> stats.feed_sub.sid AS stats_feed_sub_sid
> FROM stats.feed_sub
> WHERE stats.feed_sub.date >= '2016-06-01' :: TIMESTAMP AND
> stats.feed_sub.date <= '2016-06-30' :: TIMESTAMP AND
> stats.feed_sub.gran = '1 day'
> AND stats.feed_sub.aid = 3
> GROUP BY
> stats.feed_sub.subid, stats.feed_sub.sid;

You cast every date to an timestamp. Why? You can adjust the index to:

CREATE UNIQUE INDEX ixu_feed_sub
ON stats.feed_sub
USING btree
(date::timestamp, gran, aid, pid, sid, fid, subid COLLATE
pg_catalog."default");

But since i see no need for the cast at all (maybe i missed it) try it
without!

Greetings,
Torsten