reducing random_page_cost from 4 to 2 to force index scan

From: Sok Ann Yap <sokann(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: reducing random_page_cost from 4 to 2 to force index scan
Date: 2011-04-26 09:49:05
Message-ID: BANLkTikZhYc+h1=YDJfXwZEZc1tN3_VwCg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message J Sisson 2011-04-26 14:13:17 Re: Time to put theory to the test?
Previous Message Claudio Freire 2011-04-26 07:49:39 Re: Performance