strange query filter problems

From: "Jonas Henriksen" <jonas(dot)f(dot)henriksen(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: strange query filter problems
Date: 2006-04-19 11:27:45
Message-ID: 51518a4f0604190427u2b855886h52a37033523e6038@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I have a problem with a slow query. (I have run vacuum full analyze!)
It seems that the query below works OK because the query planner
filters on the date first. It takes about 0.3 sec:
EXPLAIN
SELECT
*
FROM sskjema s INNER JOIN tskjema t USING(sskjema_pkey)
WHERE t.species::char(12) LIKE 'TAGGMAKRELL%'::char(12)
AND s.date >=20050101

"Merge Join (cost=6.02..3899.33 rows=1 width=228)"
" Merge Cond: ("outer".sskjema_pkey = "inner".sskjema_pkey)"
" -> Index Scan using sskjema_pkey on sskjema s (cost=0.00..3868.95
rows=9738 width=157)"
" Filter: (date >= 20050101)"
" -> Sort (cost=6.02..6.03 rows=1 width=75)"
" Sort Key: t.sskjema_pkey"
" -> Index Scan using speciesix on tskjema t (cost=0.00..6.01
rows=1 width=75)"
" Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND
(species ~<~ 'TAGGMAKRELM'::bpchar))"
" Filter: (species ~~ 'TAGGMAKRELL%'::text)"

However, if I add a second date-condition to further cut the
result-size, the species-column is used as the first filter, drasticly
increasing the query-time to more than a minute:
EXPLAIN
SELECT
*
FROM sskjema s INNER JOIN tskjema t USING(sskjema_pkey)
WHERE t.species::char(12) LIKE 'TAGGMAKRELL%'::char(12)
AND s.date >=20050101
AND s.date <=20051231

"Nested Loop (cost=0.00..4049.18 rows=1 width=228)"
" Join Filter: ("inner".sskjema_pkey = "outer".sskjema_pkey)"
" -> Index Scan using speciesix on tskjema t (cost=0.00..6.01
rows=1 width=75)"
" Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND
(species ~<~ 'TAGGMAKRELM'::bpchar))"
" Filter: (species ~~ 'TAGGMAKRELL%'::text)"
" -> Index Scan using dateix on sskjema s (cost=0.00..4025.13
rows=1443 width=157)"
" Index Cond: ((date >= 20050101) AND (date <= 20051231))"

Any suggestions how to get the planner do the query in the best way?

regards Jonas:))

Responses

Browse pgsql-general by date

  From Date Subject
Next Message chris smith 2006-04-19 11:31:12 Re: How to implement a "subordinate database"?
Previous Message Kynn Jones 2006-04-19 10:19:50 How to implement a "subordinate database"?