Geek asks and answers

Are indexes stored on views?

Let's say I have a large table containing genomic positions from different files:

CREATE TABLE chromosomal_positions (
    file_id  INT,
    chromosome_id INT, 
    position INT
)

I want to compare the contents of 1 file with the contents of all other files for overlapping. Therefore, I use views.

SELECT Count(*) 
FROM   (SELECT * 
        FROM   chromosomal_positions 
        WHERE  variant_file_id = 1) AS file_1 
      JOIN (SELECT * 
            FROM   chromosomal_positions 
            WHERE  variant_file_id != 1) AS other_files 
         ON ( file_1.chromosome_id = other_files.chromosome_id 
              AND file_1.position = other_files.position )

Now, if I add a composite index to file_id, chromosome_id, a position in that order, can derived tables be able to use this index? (Using Postgres)

+3
source share
1 answer

It’s not so much that PostgreSQL “saves” sub-query indexes, since rewriting can often simplify and restructure your query so that it works directly in the base table.

; , .

SELECT count(*) 
FROM  chromosomal_positions file_1 
INNER JOIN chromosomal_positions other_files
ON ( file_1.chromosome_id = other_files.chromosome_id 
     AND file_1.position = other_files.position ) 
WHERE file1.variant_file_id = 1
AND   other_files.variant_file_id != 1;

(chromosome_id, position) .

, explain analyze, , . , , :

CREATE INDEX cp_f_c_p ON chromosomal_positions(file_id, chromosome_id , position);

-- Planner would prefer seqscan because there not really any data;
-- force it to prefer other plans.
SET enable_seqscan = off;

EXPLAIN SELECT count(*) 
FROM (
  SELECT * 
  FROM   chromosomal_positions 
  WHERE  file_id = 1
) AS file_1 
INNER JOIN (
  SELECT * 
  FROM   chromosomal_positions 
  WHERE  file_id != 1
) AS other_files 
ON ( file_1.chromosome_id = other_files.chromosome_id 
     AND file_1.position = other_files.position )

:

                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=78.01..78.02 rows=1 width=0)
   ->  Hash Join  (cost=29.27..78.01 rows=1 width=0)
         Hash Cond: ((chromosomal_positions_1.chromosome_id = chromosomal_positions.chromosome_id) AND (chromosomal_positions_1."position" = chromosomal_positions."position"))
         ->  Bitmap Heap Scan on chromosomal_positions chromosomal_positions_1  (cost=14.34..48.59 rows=1930 width=8)
               Filter: (file_id <> 1)
               ->  Bitmap Index Scan on cp_f_c_p  (cost=0.00..13.85 rows=1940 width=0)
         ->  Hash  (cost=14.79..14.79 rows=10 width=8)
               ->  Bitmap Heap Scan on chromosomal_positions  (cost=4.23..14.79 rows=10 width=8)
                     Recheck Cond: (file_id = 1)
                     ->  Bitmap Index Scan on cp_f_c_p  (cost=0.00..4.23 rows=10 width=0)
                           Index Cond: (file_id = 1)
(11 rows)

(view on explain.depesz.com)

, , . , file_id. , , :

CREATE INDEX cp_file_id ON chromosomal_positions(file_id);

, , Pg . , , , , . .

, :

CREATE INDEX cp_ci_p ON chromosomal_positions (chromosome_id, position);

, id = 1, , . , , . , .

(BTW, , " ", .. . WITH (CTE), ).

+2

More articles:


All Articles