Question

I have this for query for a table of a size of 2 700 000 rows in Heroku Postgres 12.16:

SELECT 1 AS one
FROM "users"
WHERE (
    external_id = 18
    AND (email =  user@example.com 
         OR uuid =  779c7963-67b2-43ea-b19b-028759a146dc ))
LIMIT 1

It takes 1497.968 ms to execute.

在<代码>EXPLAIN ANALYZE上填报如下:

"Limit  (cost=1000.11..41646.06 rows=1 width=4) (actual time=491.141..1497.948 rows=1 loops=1)"
"  ->  Gather  (cost=1000.11..82292.01 rows=2 width=4) (actual time=491.140..1497.946 rows=1 loops=1)"
"        Workers Planned: 1"
"        Workers Launched: 1"
"        ->  Parallel Index Only Scan using index_users_on_whitelabel_id_and_email_and_uuid on users  (cost=0.11..81291.81 rows=1 width=4) (actual time=953.762..953.762 rows=0 loops=2)"
"              Index Cond: (whitelabel_id = 18)"
"              Filter: (((email)::text =  user@example.com ::text) OR ((uuid)::text =  779c7963-67b2-43ea-b19b-028759a146dc ::text))"
"              Rows Removed by Filter: 1111474"
"              Heap Fetches: 3238"
"Planning Time: 0.139 ms"
"Execution Time: 1497.968 ms"

I see the total cost is too high (81291.81) and I suspect is the reason for the query run so slow.

Why is the reason of the total cost and how I can optimize it?

我已经运行了<代码>VACCUUM ANALYZE 用户,预计这将改进询问,但保持了同样的水平。

Answer 1

费用是估计数。高成本tcause 问题是缓慢的,仅仅反映了人们期望问题会缓慢。如果计划人正确地估计,你会渗出这么多的行文,那是认为会很缓慢的原因。

如果在<代码>(外部_id,电子邮箱)和上(外部_id, uuid)上都有单独的索引,那么你就应当能够取得一个非常有效的比图Or操作。您显然已经有了<代码>(外部_id,电子邮箱,uuid)的索引,因此,第一个索引涵盖在内,你只得加上第二版。

友情链接