postgresql - 我有什么索引到这个数据库？

Question

我有一个进行一些连接的查询。

SELECT feedback.note as notes, 
to_char(feedback.data_compilazione,'DD/MM/YYYY') as date, 
CAST(feedback.punteggio AS INT) as score, 
upper(substring(cliente.nome from '^.') || '.' || 
substring(cliente.cognome from '^.') || '.') as customer, 
testo.testo as nation FROM feedback 
JOIN prenotazione ON prenotazione.id = feedback.id_prenotazione 
JOIN cliente ON cliente.id = prenotazione.id_cliente 
JOIN struttura ON struttura.id = prenotazione.id_struttura 
JOIN lingua ON cliente.id_lingua = lingua.id 
JOIN nazione ON cliente.codice_nazione = nazione.codice_iso 
JOIN testo ON testo.nome_tabella = 'nazione' AND testo.id_record = nazione.id AND
testo.id_lingua = lingua.id AND testo.id_tipo_testo = 1 WHERE struttura.id = 43 AND
lingua.sigla = E'en' AND feedback.punteggio >= 3 AND feedback.note <> '' 
ORDER BY feedback.data_compilazione DESC LIMIT 5

我的问题是我的表没有任何明确的索引。

这意味着这个查询花了很长时间，很长很长......很长的时间来执行。

AFAIK postresql 每次声明主键时都会创建一个“隐式”索引，因此我不必“显式”添加它。

这是查询的EXPLAIN

"Limit  (cost=212.72..212.73 rows=1 width=208)"
"  ->  Sort  (cost=212.72..212.73 rows=1 width=208)"
"        Sort Key: feedback.data_compilazione"
"        ->  Nested Loop  (cost=1.11..212.71 rows=1 width=208)"
"              ->  Nested Loop  (cost=1.11..206.86 rows=1 width=212)"
"                    Join Filter: (("outer".codice_nazione)::text = ("inner".codice_iso)::text)"
"                    ->  Nested Loop  (cost=1.11..201.63 rows=1 width=223)"
"                          ->  Nested Loop  (cost=1.11..195.60 rows=1 width=187)"
"                                Join Filter: ("outer".id = "inner".id_cliente)"
"                                ->  Nested Loop  (cost=1.11..45.18 rows=1 width=183)"
"                                      Join Filter: ("outer".id_lingua =    "inner".id_lingua)"
"                                      ->  Index Scan using testo_pkey on testo  (cost=0.00..6.27 rows=1 width=40)"
"                                            Index Cond: (((nome_tabella)::text = 'nazione'::text) AND (id_tipo_testo = 1))"
"                                      ->  Hash Join  (cost=1.11..38.86 rows=4 width=155)"
"                                            Hash Cond: ("outer".id_lingua = "inner".id)"
"                                            ->  Seq Scan on cliente  (cost=0.00..33.47 rows=847 width=151)"
"                                            ->  Hash  (cost=1.11..1.11 rows=1 width=4)"
"                                                  ->  Seq Scan on lingua  (cost=0.00..1.11 rows=1 width=4)"
"                                                        Filter: ((sigla)::text = 'en'::text)"
"                                ->  Seq Scan on prenotazione  (cost=0.00..150.05 rows=30 width=12)"
"                                      Filter: (43 = id_struttura)"
"                          ->  Index Scan using feedback_id_prenotazione_key on feedback  (cost=0.00..6.01 rows=1 width=44)"
"                                Index Cond: ("outer".id = feedback.id_prenotazione)"
"                                Filter: ((punteggio >= 3::double precision) AND (note <> ''::text))"
"                    ->  Index Scan using nazione_pkey on nazione  (cost=0.00..5.21 rows=1 width=11)"
"                          Index Cond: ("outer".id_record = nazione.id)"
"              ->  Index Scan using struttura_pkey on struttura  (cost=0.00..5.82 rows=1 width=4)"
"                    Index Cond: (id = 43)"

所以我停止考虑在 DB 上建立索引。创建索引的最佳实践是什么？解决方案是:为每个连接的字段创建一个索引？

在我的数据库中，你建议索引什么？

我已经做了一些尝试(基本上索引每个连接的字段)并且查询现在运行得更快但不快(大约六秒检索零行)。我想我的解决方案不是最好的。

有人能指出我的看法吗？

编辑

如果我只添加一个索引(在 prenotazione.id_cliente 上)，所有操作都将在几秒钟内完成(大约 1.5 秒)。那么，为什么在外键上添加所有索引会使我的查询运行速度变慢？

Answer 1

作为第一条经验法则，即使不查看查询计划，我肯定会将索引放在具有高选择性(>100 个不同值)的外键上。

有些数据库不问就把它们放了，而 Postgres 不会。我们谈论的是外键索引，而不是主键索引(显然总是提供这些索引)。

例如，prenotazione.id_struttura 和 feedback.id_prenotazione 可能是候选对象。

另一方面，包含 7 个不同值的 weekday 列不会从索引中获益，除非有数千个星期一和很少的星期日，在这种情况下，索引对某些值具有选择性.