我有两个表,它们彼此链接如下:

answered_questions 包含以下列和索引:

• id
• taken_test_id :整数(外键)
• question_id questions
• indexes :(参加了测试,问题)

表 taken_tests

• 身份证件
• user_id :(外键,指向表用户的链接)
• 索引: 用户id 柱

第一个查询(带解释分析输出):

EXPLAIN ANALYZE 
SELECT 
  "answered_questions".* 
FROM 
  "answered_questions" 
  INNER JOIN "taken_tests" ON "answered_questions"."taken_test_id" = "taken_tests"."id" 
WHERE 
  "taken_tests"."user_id" = 1;

Nested Loop  (cost=0.99..116504.61 rows=1472 width=61) (actual time=0.025..2.208 rows=653 loops=1)
   ->  Index Scan using index_taken_tests_on_user_id on taken_tests  (cost=0.43..274.18 rows=91 width=4) (actual time=0.014..0.483 rows=371 loops=1)
         Index Cond: (user_id = 1)
   ->  Index Scan using index_answered_questions_on_taken_test_id_and_question_id on answered_questions  (cost=0.56..1273.61 rows=365 width=61) (actual time=0.00
2..0.003 rows=2 loops=371)
         Index Cond: (taken_test_id = taken_tests.id)
 Planning time: 0.276 ms
 Execution time: 2.365 ms
(7 rows)

另一个查询(当使用 joins (ActiveRecord中的方法)

EXPLAIN ANALYZE 
SELECT 
  "answered_questions".* 
FROM 
  "answered_questions" 
  INNER JOIN "taken_tests" ON "taken_tests"."id" = "answered_questions"."taken_test_id" 
WHERE 
  "taken_tests"."user_id" = 1;

这是输出

Nested Loop  (cost=0.99..116504.61 rows=1472 width=61) (actual time=23.611..1257.807 rows=653 loops=1)
   ->  Index Scan using index_taken_tests_on_user_id on taken_tests  (cost=0.43..274.18 rows=91 width=4) (actual time=10.451..71.474 rows=371 loops=1)
         Index Cond: (user_id = 1)
   ->  Index Scan using index_answered_questions_on_taken_test_id_and_question_id on answered_questions  (cost=0.56..1273.61 rows=365 width=61) (actual time=2.07
1..3.195 rows=2 loops=371)
         Index Cond: (taken_test_id = taken_tests.id)
 Planning time: 0.302 ms
 Execution time: 1258.035 ms
(7 rows)

内部连接 条件。在第一个查询中,它是 ON "answered_questions"."taken_test_id" = "taken_tests"."id" 在第二个查询中,它是 ON "taken_tests"."id" = "answered_questions"."taken_test_id" . 但是查询时间有很大的不同。

你知道为什么会这样吗?我读了一些文章,其中说连接条件中列的顺序不应该影响执行时间(例如: Best practices for the order of joined columns in a sql join? )

我用的是Postgres9.6。有超过4000万排在 表中有超过300万行 参加了大学考试 桌子

(analyze true, verbose true, buffers true)

EXPLAIN (ANALYZE TRUE, VERBOSE TRUE, BUFFERS TRUE) 
SELECT
  "answered_questions".* 
FROM
  "answered_questions"
  INNER JOIN "taken_tests" ON "taken_tests"."id" = "answered_questions"."taken_test_id" 
WHERE
  "taken_tests"."user_id" = 1;

输出

Nested Loop  (cost=0.99..116504.61 rows=1472 width=61) (actual time=0.030..2.192 rows=653 loops=1)
   Output: answered_questions.id, answered_questions.question_id, answered_questions.answer_text, answered_questions.created_at, answered_questions.updated_at, a
nswered_questions.taken_test_id, answered_questions.correct, answered_questions.answer
   Buffers: shared hit=1986
   ->  Index Scan using index_taken_tests_on_user_id on public.taken_tests  (cost=0.43..274.18 rows=91 width=4) (actual time=0.014..0.441 rows=371 loops=1)
         Output: taken_tests.id
         Index Cond: (taken_tests.user_id = 1)
         Buffers: shared hit=269
   ->  Index Scan using index_answered_questions_on_taken_test_id_and_question_id on public.answered_questions  (cost=0.56..1273.61 rows=365 width=61) (actual ti
me=0.002..0.003 rows=2 loops=371)
         Output: answered_questions.id, answered_questions.question_id, answered_questions.answer_text, answered_questions.created_at, answered_questions.updated
_at, answered_questions.taken_test_id, answered_questions.correct, answered_questions.answer
         Index Cond: (answered_questions.taken_test_id = taken_tests.id)
         Buffers: shared hit=1717
 Planning time: 0.238 ms
 Execution time: 2.335 ms