Re: Bad query performance with more conditions?

On 24 Jun 2010, at 10:52, kaifeng.zhu wrote:

> Hi All,
>
> I have a table named emails which created by following script:
> create table emails (
> email_id bigserial primary key,
> email_msg_id char(36) not null,
> email_sender text not null)
>
> The table has more than 10 million of records and I have already
> created indexes on email_msg_id and email_sender
>
> Following script finished very quickly (less than 1 seconds):
> select * from emails where email_msg_id =
> '4dba381b-f55e-02d3-4b4a-95e2e98178e2' limit 1
> But a similar script cost more than ten seconds:
> select * from emails where email_msg_id =
> '4dba381b-f55e-02d3-4b4a-95e2e98178e2' and email_sender_text =
> 'sender(at)domain(dot)com' limit 1
>
> Both those scripts returns 0 rows of records indeed.
>
> Any comment are highly appreciated.

You didn't provide explain analyse results for those queries, so I'm just guessing here, but I think you should add indices to email_msg_id and email_sender_text to speed up those queries.

Actually, from your results it would appear that you did create an index on the former, while you probably didn't on the latter, causing the second query to have to scan sequentially through all records.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4c2321f5286211760940954!