GROUP BY

analyze_explain_group_by.sql

"HashAggregate  (cost=142395.01..142395.21 rows=20 width=4) (actual time=1693.497..1693.500 rows=21 loops=1)"
"  ->  Seq Scan on tracked_points  (cost=0.00..121326.67 rows=4213667 width=4) (actual time=0.006..665.009 rows=4206624 loops=1)"
"Total runtime: 1693.546 ms"

analzye_explain_count.sql

"Aggregate  (cost=45701.18..45701.19 rows=1 width=0) (actual time=34243.868..34243.869 rows=1 loops=1)"
"  ->  Index Only Scan using index_tracked_points_on_worker_id on tracked_points  (cost=0.43..43884.38 rows=726717 width=0) (actual time=53.278..34152.984 rows=718069 loops=1)"
"        Index Cond: (worker_id = 5)"
"        Heap Fetches: 11"
"Total runtime: 34243.910 ms"

count.sql

EXPLAIN ANALYZE SELECT COUNT(*) FROM tracked_points WHERE worker_id = 5

explain_analyze_buffer_where.sql

"Aggregate  (cost=45701.18..45701.19 rows=1 width=0) (actual time=189.194..189.194 rows=1 loops=1)"
"  Buffers: shared hit=6405"
"  ->  Index Only Scan using index_tracked_points_on_worker_id on tracked_points  (cost=0.43..43884.38 rows=726717 width=0) (actual time=0.020..120.385 rows=718069 loops=1)"
"        Index Cond: (worker_id = 5)"
"        Heap Fetches: 11"
"        Buffers: shared hit=6405"
"Total runtime: 189.223 ms"

explain_analyze_buffers_group.sql

"HashAggregate  (cost=142395.01..142395.21 rows=20 width=4) (actual time=2685.046..2685.051 rows=21 loops=1)"
"  Buffers: shared hit=2448 read=76742"
"  ->  Seq Scan on tracked_points  (cost=0.00..121326.67 rows=4213667 width=4) (actual time=0.005..1620.925 rows=4206624 loops=1)"
"        Buffers: shared hit=2448 read=76742"
"Total runtime: 2685.092 ms"

group_by.sql

EXPLAIN ANALYZE SELECT COUNT(*), worker_id FROM tracked_points GROUP BY worker_id

tracked_point.sql

-- Table: public.tracked_points

-- DROP TABLE public.tracked_points;

CREATE TABLE public.tracked_points
(
  id integer NOT NULL DEFAULT nextval('tracked_points_id_seq'::regclass),
  recorded_at timestamp without time zone,
  worker_id integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  location_id integer NOT NULL,
  session_id integer,
  frequency integer,
  geom geometry(PointZ),
  worker_id_checked_at timestamp without time zone,
  zone_checked_at timestamp without time zone,
  CONSTRAINT tracked_points_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.tracked_points
  OWNER TO tyler;

-- Index: public.index_tracked_points_on_geom

-- DROP INDEX public.index_tracked_points_on_geom;

CREATE INDEX index_tracked_points_on_geom
  ON public.tracked_points
  USING gist
  (geom);

-- Index: public.index_tracked_points_on_recorded_at

-- DROP INDEX public.index_tracked_points_on_recorded_at;

CREATE INDEX index_tracked_points_on_recorded_at
  ON public.tracked_points
  USING btree
  (recorded_at);

-- Index: public.index_tracked_points_on_worker_id

-- DROP INDEX public.index_tracked_points_on_worker_id;

CREATE INDEX index_tracked_points_on_worker_id
  ON public.tracked_points
  USING btree
  (worker_id);

-- Index: public.index_tracked_points_on_worker_id_checked_at

-- DROP INDEX public.index_tracked_points_on_worker_id_checked_at;

CREATE INDEX index_tracked_points_on_worker_id_checked_at
  ON public.tracked_points
  USING btree
  (worker_id_checked_at);

-- Index: public.index_tracked_points_on_zone_checked_at

-- DROP INDEX public.index_tracked_points_on_zone_checked_at;

CREATE INDEX index_tracked_points_on_zone_checked_at
  ON public.tracked_points
  USING btree
  (zone_checked_at);

-- Index: public.index_tracked_points_partial_zone_checked_at

-- DROP INDEX public.index_tracked_points_partial_zone_checked_at;

CREATE INDEX index_tracked_points_partial_zone_checked_at
  ON public.tracked_points
  USING btree
  (zone_checked_at);