pg_repack to order one table by a timestamp column, however I'm finding that after running
pg_repack command it's not ordered at all.
I'm using this command
pg_repack --jobs 4 -t table_with_dates -e -o "start_time asc"
After about 10 minutes it finishes but when I query the first page of the table there is no order over "start_time" column.
I'm using postgresql 9.6.3 and pg_repack 1.4.0
Is there something i'm missing here so I can get the correct data ordering?
Edit 1: Table definition
CREATE TABLE customer_service ( customer_service_id character varying(32) NOT NULL, store_id character varying(32) NOT NULL, start_time timestamp without time zone NOT NULL, end_time timestamp without time zone NOT NULL, customer_id character varying(32) NOT NULL, service_id character varying(32) NOT NULL, CONSTRAINT customer_service_key PRIMARY KEY (customer_service_id), ); CREATE INDEX customer_service_start_idx ON public.customer_service USING btree (start_time); CREATE INDEX customer_service_customer_idx ON public.customer_service USING btree(customer_id); CREATE INDEX customer_service_service_idx ON public.customer_service USING btree (service_id);
SELECT performed by
LOG: (query) CREATE TABLE repack.table_6527203 WITH (oids = false) TABLESPACE pg_default AS SELECT customer_service_id, store_id, start_time, end_time, customer_id, service_id FROM ONLY customer_service ORDER BY start_time WITH NO DATA LOG: (query) INSERT INTO repack.table_6527203 SELECT customer_service_id, store_id, start_time, end_time, customer_id, service_id FROM ONLY customer_service
Seems like the insert into is not ordering the data?, as the order by is only present on the create table command
Edit 2: If I run
WITH dates_per_page AS ( SELECT (ctid::text::point)::bigint AS page_number, COUNT(DISTINCT date(start_time)) FROM customer_service GROUP BY 1 ) SELECT avg(count) FROM dates_per_page;
I get 15.340 as the result, I would expect a number closer to 1 here...