Skip site navigation (1) Skip section navigation (2)

PostgreSQL 9.1 : why is this query slow?

From: Joost Kraaijeveld <J(dot)Kraaijeveld(at)Askesis(dot)nl>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL 9.1 : why is this query slow?
Date: 2011-11-28 16:42:06
Message-ID: 1322498526.3343.22.camel@panoramix.Askesis.nl (view raw or flat)
Thread:
Lists: pgsql-performance
Hi All,

I have a table with 665605 rows (counted, vacuum-ed):
CREATE TABLE unique_words
( filename text NOT NULL,
  filetype text NOT NULL,
  word text NOT NULL,
  count integer,)

The query is:
select f.word , count(f.word) from 
unique_words as f, 
unique_words as s ,
unique_words as n 
where
(f.word = s.word and s.word = n.word)
and
(f.filetype = 'f' and s.filetype = 's' and n.filetype = 'n')
group by f.word

Explain says:
"GroupAggregate  (cost=0.00..67237557.88 rows=1397 width=6)"
"  ->  Nested Loop  (cost=0.00..27856790.31 rows=7876150720 width=6)"
"        ->  Nested Loop  (cost=0.00..118722.04 rows=14770776 width=12)"
"              ->  Index Scan using idx_unique_words_filetype_word on unique_words f  (cost=0.00..19541.47 rows=92098 width=6)"
"                    Index Cond: (filetype = 'f'::text)"
"              ->  Index Scan using idx_unique_words_filetype_word on unique_words s  (cost=0.00..0.91 rows=13 width=6)"
"                    Index Cond: ((filetype = 's'::text) AND (word = f.word))"
"        ->  Index Scan using idx_unique_words_filetype_word on unique_words n  (cost=0.00..1.33 rows=44 width=6)"
"              Index Cond: ((filetype = 'n'::text) AND (word = f.word))"


The right answer should be 3808 different words (according to a Java
program I wrote).

This query takes more than 1 hour (after which I cancelled the query).
My questions are:
- Is this to be expected?
- Especially as the query over just 1 join takes 32 secs? (on f.word =
s.word omitting everything for n )
- Why does explain say it takes "7876150720 rows"? 
- Is there a way to rephrase the query that makes it faster?
- Could another table layout help (f,s,n are all possibilities for
filetype)?
- Anything else?????

TIA

-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl


Responses

pgsql-performance by date

Next:From: Joost KraaijeveldDate: 2011-11-28 16:57:49
Subject: Re: PostgreSQL 9.1 : why is this query slow?
Previous:From: Maxim BogukDate: 2011-11-28 00:05:57
Subject: Re: Some question about lazy subquery/procedures execution in SELECT ... ORDER BY... LIMIT N queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group