reducing random_page_cost from 4 to 2 to force index scan

Поиск
Список
Период
Сортировка
От Sok Ann Yap
Тема reducing random_page_cost from 4 to 2 to force index scan
Дата
Msg-id BANLkTikZhYc+h1=YDJfXwZEZc1tN3_VwCg@mail.gmail.com
обсуждение исходный текст
Ответы Re: reducing random_page_cost from 4 to 2 to force index scan  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Список pgsql-performance
Hi,

I am using PostgreSQL 9.0. There is a salutations table with 44 rows,
and a contacts table with more than a million rows. The contacts table
has a nullable (only 0.002% null) salutation_id column, referencing
salutations.id.

With this query:

SELECT
    salutations.id,
    salutations.name,
    salutations.description,
    EXISTS (
        SELECT 1
        FROM contacts
        WHERE salutations.id = contacts.salutation_id
    ) AS in_use
FROM salutations

I have to reduce random_page_cost from 4 to 2 to force index scan.

EXPLAIN ANALYSE output with random_page_cost = 4:

 Seq Scan on salutations  (cost=0.00..50.51 rows=44 width=229) (actual
time=0.188..3844.037 rows=44 loops=1)
   SubPlan 1
     ->  Seq Scan on contacts  (cost=0.00..64578.41 rows=57906
width=0) (actual time=87.358..87.358 rows=1 loops=44)
           Filter: ($0 = salutation_id)
 Total runtime: 3844.113 ms

EXPLAIN ANALYSE output with random_page_cost = 4, enable_seqscan = 0:

 Seq Scan on salutations  (cost=10000000000.00..10000000095.42 rows=44
width=229) (actual time=0.053..0.542 rows=44 loops=1)
   SubPlan 1
     ->  Index Scan using ix_contacts_salutation_id on contacts
(cost=0.00..123682.07 rows=57906 width=0) (actual time=0.011..0.011
rows=1 loops=44)
           Index Cond: ($0 = salutation_id)
 Total runtime: 0.592 ms

EXPLAIN ANALYSE output with random_page_cost = 2:

 Seq Scan on salutations  (cost=0.00..48.87 rows=44 width=229) (actual
time=0.053..0.541 rows=44 loops=1)
   SubPlan 1
     ->  Index Scan using ix_contacts_salutation_id on contacts
(cost=0.00..62423.45 rows=57906 width=0) (actual time=0.011..0.011
rows=1 loops=44)
           Index Cond: ($0 = salutation_id)
 Total runtime: 0.594 ms

So, index scan wins by a very small margin over sequential scan after
the tuning. I am a bit puzzled because index scan is more than 3000
times faster in this case, but the estimated costs are about the same.
Did I do something wrong?

Regards,
Yap

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tomas Vondra
Дата:
Сообщение: Re: Performance
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: reducing random_page_cost from 4 to 2 to force index scan