How to handle joins between huge tables in PostgreSQL?
===

I have two tables:
urls (table with indexed pages, host is indexed column, 30 mln rows)
hosts (table with information about hosts, host is indexed column, 1mln rows)

One of the most frequent SELECT in my application is:
SELECT urls.* FROM urls JOIN hosts ON urls.host = hosts.host WHERE urls.projects_id = ? AND hosts.is_spam IS NULL ORDER by urls.id DESC, LIMIT ?
In projects which have more than 100 000 rows in urls table the query executes very slow.

Since the tables has grown the query is execution slower and slower. I've read a lot about NoSQL databases (like MongoDB) which are designed to handle so big tables but changing my database from PgSQL to MongoDB is for me big issue. Right now i would like try to optimize PgSQL solution. Do you have any advice for? What should i do?



