Re: Slow query with a lot of data

Am 18.08.2008 um 16:30 schrieb Matthew Wakeling:

> 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
>

Thanks, the index was created but I forgot to run analyze again on
that table.

I had a little mistake in my previous sql query. The corrected version
is this:
explain analyze select a."user", b.category, count(1) from result a,
domain_categories b where a."domain" = b."domain" and a."user" = 1337
group by a."user", b.category;

(notice the additional group by column).

explain analyze:


"HashAggregate  (cost=817397.78..817428.92 rows=2491 width=8) (actual
time=42874.339..42878.419 rows=3361 loops=1)"
"  ->  Merge Join  (cost=748.47..674365.50 rows=19070970 width=8)
(actual time=15702.449..42829.388 rows=36308 loops=1)"
"        Merge Cond: (b.domain = a.domain)"
"        ->  Index Scan using domain_categories_domain on
domain_categories b  (cost=0.00..391453.79 rows=12105014 width=8)
(actual time=39.018..30166.349 rows=12104989 loops=1)"
"        ->  Sort  (cost=395.52..405.49 rows=3985 width=8) (actual
time=0.188..32.345 rows=36309 loops=1)"
"              Sort Key: a.domain"
"              Sort Method:  quicksort  Memory: 27kB"
"              ->  Index Scan using result_user_idx on result a
(cost=0.00..157.21 rows=3985 width=8) (actual time=0.021..0.101
rows=61 loops=1)"
"                    Index Cond: ("user" = 1337)"
"Total runtime: 42881.382 ms"

This is still very slow...