From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Query not using the indexes properly. |
Date: | 2009-10-02 01:47:23 |
Message-ID: | 855e4dcf0910011847q2779cbdfpdff04d8b742b6270@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a pretty simple query on a pretty simple table with about 60
million records in it.
This is the query.
SELECT * FROM "changes" WHERE (id > 1935759 and company_id = 4 and
source_model_name = 'CommissionedVisit') ORDER BY id ASC LIMIT 1
The id field is the primary key. The other fields are indexed
(company_id and source_model_name).
This query takes about 30 seconds to run on a pretty beefy machine.
Here is the explain.
"Limit (cost=0.00..7.46 rows=1 width=45) (actual
time=28799.712..28799.712 rows=0 loops=1)"
" -> Index Scan using changes_pkey on changes
(cost=0.00..2331939.52 rows=312519 width=45) (actual
time=28799.710..28799.710 rows=0 loops=1)"
" Index Cond: (id > 1935759)"
" Filter: ((company_id = 4) AND ((source_model_name)::text =
'CommissionedVisit'::text))"
"Total runtime: 28799.749 ms"
It seem to me that it's ignoring the indexes on the text fields. Is
that right?
I did a vacuum analyze on the table but that didn't seem to help at all.
From | Date | Subject | |
---|---|---|---|
Next Message | Chris | 2009-10-02 02:04:29 | Re: Query not using the indexes properly. |
Previous Message | Alvaro Herrera | 2009-10-01 23:51:00 | Re: Weird behavior with "sensitive" cursors. |