I'm using 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 pg_repack
:
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)[0]::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…
Best Answer
This problem is fixed on the version 1.4.1 of pg_repack.
github issue for reference