optimize Query in PostgreSQL [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

optimize Query in PostgreSQL

SELECT count(*) 
FROM contacts_lists 
     JOIN plain_contacts 
          ON contacts_lists.contact_id = plain_contacts.contact_id 
     JOIN contacts 
          ON contacts.id = plain_contacts.contact_id 
WHERE plain_contacts.has_email 
      AND NOT contacts.email_bad 
      AND NOT contacts.email_unsub 
      AND contacts_lists.list_id =67开发者_开发知识库339

In here contacts_lists.contact_id and contacts_lists.list_id are indexed how to optimize this query?

---

You probably want to index either contacts.id or plain_contacts.contact_id to speed up the join. One of the two fields should actually be a primary key.

If this is not sufficient, you will probably need to refactor the database. Why is there contacts and plain_contacts tables to begin with?

---

Since you only want to inlude rows that has some flags set in the joined tables, I would move that statements into the join clause:

SELECT count(*) 
FROM contacts_lists 
     JOIN plain_contacts 
          ON contacts_lists.contact_id = plain_contacts.contact_id 
          AND NOT plain_contacts.has_email
     JOIN contacts 
          ON contacts.id = plain_contacts.contact_id 
          AND NOT contacts.email_unsub 
          AND NOT contacts.email_bad 
WHERE contacts_lists.list_id =67339

I'm not sure if this would make a great impact on performance, but worth a try. You should probably have indexes on the joined tables as well for optimal performance, like this:

plain_contacts: contact_id, has_email
contacts: id, email_unsub, email_bad