0

I have a table with this definition:

CREATE TABLE public.delete_operation
(
  id bigint NOT NULL,
  parent_id bigint,
  pdu character varying,
  progress smallint NOT NULL DEFAULT 0,
  description character varying,
  state character varying(31) NOT NULL,
  status character(1) NOT NULL DEFAULT 'A'::bpchar,
  CONSTRAINT delete_operation_pkey PRIMARY KEY (id),
  CONSTRAINT "Ref_delete_operation_to_delete_operation" FOREIGN KEY (parent_id)
      REFERENCES public.delete_operation (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Index on id created implicitly.

Then I want to create an index on parent_id (which refers to same id column with already created index).

My question is: would it be physically same index with different names or will it double this index ?

2

How can the 2 indexes be physically the same? The values of id could be more example:

1,2,3,4,6,7,8,9

while the parent_id values could be:

NULL,1,1,8,NULL,NULL,2,NULL

So the answer to the question is: No, they would be two completely separate and different structures. It doesn't make any other sense. Even if the parent_id were all, one by one, identical to the id values, it still would not make any sense either. Because the server cannot know or be sure that the values will be always identical. Rows can be updated and new rows with different id - parent_id values can be inserted. So two separate indexes have to be used.

| improve this answer | |
0

Even 2 same indices on same column would be 2 different physical indices. You can check the index file name with :

select relfilenode, relname from pg_class where relname = 'index_name';

Correlate the physical files in :

~PGDATA/base/db_oid/relfilenode
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.