Question: PostgreSQL Slow query, Order by LIMIT

Question

PostgreSQL Slow query, Order by LIMIT

Answers 1
Added at 2016-12-01 02:12
Tags
Question
  • List item

mydbab=# \d+ table1;

                   Table "dbmydb.table1"
     Column     |  Type   | Modifiers | Scol5rage  | Stats target | Description 
----------------+---------+-----------+------------+--------------+-------------
 pk             | bigint  | not null  | plain      |              |   
 col1           | bigint  | not null  | plain      |              |   
 col2           | citext  | not null  | extended   |              |   
 col3           | citext  |           | extended   |              |        
 col4           | citext  | not null  | extended   |              |   
 col5           | citext  |           | extended   |              |   
 col6           | citext  |           | extended   |              |   
 bcol7          | citext  |           | extended   |              |   
 col8           | citext  |           | extended   |              |   
 col9           | citext  |           | extended   |              |  
 col10          | bigint  | not null  | plain      |              |   
 col11          | citext  |           | extended   |              |   
 col12          | bigint  |           | plain      |              |     
 col13          | integer | default 1 | plain      |              |   
 col14          | integer | default 0 | plain      |              |   
 col15          | bigint  |           | plain      |              |   
 col16          | integer | default 0 | plain      |              |   
 col17          | integer | default 0 | plain      |              |     
 col18          | integer | default 0 | plain      |              |   
 col19          | citext  |           | extended   |              |   
 col20          | citext  |           | extended   |              |   
 col21          | citext  |           | extended   |              | 

Indexes:

"table1_pk" PRIMARY KEY, btree (pk)  
"table1_idx3" btree (col10)  
"table1_idx4" btree ("col4")  
"table1_idx5" btree (col11)  
"table1_idx6" btree (col15)  
"table1_fk_idx" btree (col1)

Check constraints:

"table1_col6_c" CHECK (length(col6::text) <= 5000)
"table1_col4_c" CHECK (length("col4"::text) <= 253)
"table1_col5_c” CHECK (length(col5::text) <= 5000)
"table1_col3_c" CHECK (length(col3::text) <= 253)
"table1_col11_c" CHECK (length(col11::text) <= 253)
"table1_col20_c” CHECK (length(col20::text) <= 200)
"table1_col21_c” CHECK (length(col21::text) <= 500)
"table1_col7_c” CHECK (length(col7::text) <= 500)
"table1_col9_c" CHECK (length(col9::text) <= 100)
"table1_col5_c" CHECK (length("col5"::text) <= 5000)
"table1_col19_c” CHECK (length(col19::text) <= 100)

Foreign-key constraints:

"table1_fk" FOREIGN KEY (col1) REFERENCES table2(col1) ON DELETE CASCADE

Referenced by:

TABLE “table3” CONSTRAINT “table3_fk2" FOREIGN KEY (pk) REFERENCES table1(pk) ON DELETE CASCADE  
TABLE “table4” CONSTRAINT “table4_fk2" FOREIGN KEY (pk) REFERENCES table1(pk) ON DELETE CASCADE  
TABLE “table5” CONSTRAINT “table5_fk1" FOREIGN KEY (pk) REFERENCES table1(pk)  
TABLE “table6” CONSTRAINT “table6_fk" FOREIGN KEY (pk) REFERENCES table1(pk) ON DELETE CASCADE  
TABLE “table7” CONSTRAINT “table7_fk1" FOREIGN KEY (pk) REFERENCES table1(pk) ON DELETE CASCADE  

Options: autovacuum_vacuum_scale_factor=0.05

QUERY PLAN

explain SELECT * FROM table1 WHERE ((table1.col1 = 814000000002054) AND (((table1.pk >= 238000000000000) AND (table1.pk <= 238999999999999)) OR  ((table1.pk >= 0) AND (table1.pk <= 999999999999))))   ORDER BY table1.col15 DESC LIMIT 7
(query gets timed out for explain analyze) 

Limit (cost=0.56..607.64 rows=7 width=603)

Index Scan Backward using table1_idx6 on table1 (cost=0.56..12622561.13 rows=145548 width=603) Filter: ((col1 = 814000000002054::bigint) AND (((pk >= 238000000000000::bigint) AND (pk <= 238999999999999::bigint)) OR ((pk >= 0) AND (pk <= 999999999999::bigint))))

Problem: Table has got 50M records. This query is taking more than 15minutes to run! We have calibrated to auto vacuum to 0.05. Auto analyse is in its PG’s default. the query was even changed to ORDER BY table1.col15 ASC LIMIT 7, even the the query is taking around the same time. Insert/Update operation takes only ms.

History: The result used to be faster when it has lesser data - Query had no issues in MySQL, migrated to Pg few weeks back.

Worker_mem is 956MB.

select version();

 PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-54), 64-bit
(1 row)
Answers
nr: #1 dodano: 2016-12-01 02:12

Try create a multiple-column index:

CREATE INDEX ON table1(col15, col1, pk);
Source Show
◀ Wstecz