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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
