Re: less than 2 sec for response - possible? - Mailing list pgsql-performance

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