How to speed up request 'SELECT'?

user_8569991 0
Viewed 71

I have a large table (90 GB) with data:

       col1     col2
----------------------
   1   str1     one
   2   str2     two
   3   str3     three
   4   str2     two
   5   str1     seven
   6   str4     seven
   7   str3     three

col1 is character varying type, and col2 is varchar type. The col1_idx index has been created on the col1 column.

In order not to perform separately many queries like:

SELECT * FROM table_name WHERE col1='str1';

I making one query to find matches on the col1 column like so:

SELECT * FROM table_name WHERE col1 in ('str1', 'str4')

As a result of the request, I get:

['str1:one', 'str1:seven', 'str4:seven']

But both in the first and in the second cases, queries are executed very slowly (>20 min). Computer resources (processor, memory and hard disk) are hardly used. Tell me how to fix this query or tune the database to speed up the search for matches on the col1 column? I have PostgresQL 12 database installed.

UPD1: Executed a small query (EXPLAIN ANALYZE SELECT) of 120,000 rows in small DB with size 2Gb (for example):

.....[]))">, <Record QUERY PLAN='Planning Time: 88.939 ms'>, <Record QUERY PLAN='Execution Time: 3026.371 ms'>]
postgresql
2 Answers

Use an index-only scan by including the other column(s) in the index. This way, there is no need for table lookup.

For example:

CREATE INDEX table_name ON table_name(col1) INCLUDE (col2);
user_9676210

Using an index will increase performance if the col1 values are selective for "str1" and "str4"

Additionally, to reduce the table size, it is best practice to use the character length of the columns as small as possible

user_8329910
Related