-1

I have this table in PostgreSQL database with 6 millions for rows.

CREATE TABLE IF NOT EXISTS public.processed
(
    id bigint NOT NULL DEFAULT nextval('processed_id_seq'::regclass),
    created_at timestamp without time zone,
    word character varying(200) COLLATE pg_catalog."default",
    score double precision,
    updated_at timestamp without time zone,
    is_domain_available boolean,
    CONSTRAINT processed_pkey PRIMARY KEY (id),
    CONSTRAINT uk_tb03fca6mojpw7wogvaqvwprw UNIQUE (word)
)

I want to optimize it for performance like adding index for column and add partitioning. Should I add index only for column word or it should be better to add it for several columns. What is the recommended to partition this table? Are there other recommended ways like adding compression for example to do some optimization?

Peter Penzov
  • 1,126
  • 134
  • 430
  • 808
  • 3
    The index needs to suit the query. You haven't shown any queries. Also, there is no column named "keyword" shown. – jjanes Jul 30 '22 at 18:54
  • I made a post update. I use simple select, update and delete queries only. – Peter Penzov Jul 30 '22 at 19:06
  • 1
    Without a query, there is nothing to optimize. No index will make any difference. Use explain (analyze, verbose, buffers) for your queries to get the query plans, that will be your starting point for performance optimisations – Frank Heikens Jul 31 '22 at 00:44
  • 1
    You added a bounty to this question, but no specific query to optimize. It doesn't seem like you understood the comments above. – Bill Karwin Aug 26 '22 at 16:01
  • @PeterPenzov, when you select, update and delete, what is the key in the WHERE? You choose records based on field "id" or "word"? – user_0 Aug 30 '22 at 10:30
  • it's based in word – Peter Penzov Aug 30 '22 at 11:49
  • @PeterPenzov Did you then try adding index on `word` column (as your first instinct), and see if it optimised your query? – Rohit Jain Aug 31 '22 at 05:01

1 Answers1

1

First there is no compression, nor columnar indexes in PostGreSQL, like other RBBMS that have those features (as an example Microsoft SQL have 4 ways to compress data without needs to decompress to read or seek, and can use columstore indexes). For columnar indexes you have to go on the Fujistu PG version that cost a lot...

https://www.postgresql.fastware.com/in-memory-columnar-index-brochure

So the only ways you have to accelerates some accesses to seeks on "word" column are :

  • storing a hash of the word in an additionnal column and use this colums to do searches after having indexed it
  • effectively use a partitionning with an equilibrate split like sanborn cutter tables

And finally combine the two options.

SQLpro
  • 3,994
  • 1
  • 6
  • 14