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