Re: Slow query with a lot of data

On Mon, 18 Aug 2008, Moritz Onken wrote:
> I have indexes on result.domain, domain_categories.domain, result.user,
> domain_categories.category. Clustered result on user and domain_categories on
> domain.

> "        ->  Materialize  (cost=2118752.28..2270064.64 rows=12104989 width=8)
> (actual time=46460.599..82336.116 rows=12123161 loops=1)"
> "              ->  Sort  (cost=2118752.28..2149014.75 rows=12104989 width=8)
> (actual time=46460.592..59595.851 rows=12104989 loops=1)"
> "                    Sort Key: b.domain"
> "                    Sort Method:  external sort  Disk: 283992kB"
> "                    ->  Seq Scan on domain_categories b
> (cost=0.00..198151.89 rows=12104989 width=8) (actual time=14.352..22572.869
> rows=12104989 loops=1)"

This is weird, given you say you have clustered domain_categories on
domain. Have you analysed? You should be able to run:

EXPLAIN SELECT * from domain_categories ORDER BY domain

and have it say "Index scan" instead of "Seq Scan followed by disc sort)".

Matthew

--
Patron: "I am looking for a globe of the earth."
Librarian: "We have a table-top model over here."
Patron: "No, that's not good enough. Don't you have a life-size?"
Librarian: (pause) "Yes, but it's in use right now."