Here is my table definition
-- Table: public.my_table -- DROP TABLE public.my_table; CREATE TABLE public.my_table ( my_row_id text COLLATE pg_catalog."default" NOT NULL, my_item_id text COLLATE pg_catalog."default" NOT NULL, my_timestamp timestamp with time zone NOT NULL DEFAULT now(), -- ...... some other data ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE public.my_table OWNER to my_db;
The cardinality of the table is about 10-20 million rows
Here is the query I want to speed up
select my_row_id, my_item_id from my_table where my_timestamp > timestamp '2018-09-13 00:00:00' order by my_timestamp desc
I am tempted to just index the timestamp, but I worry that since the precision of timestamp is millisecond, this means the the cardinality of the index itself will be almost as large as the cardinality of the table (eg. the number of rows will be equal to the number of index entry)
Should I have custom index that index with the precision of a day?
Like store an extra column
timestamp with timezone
And index the text instead?