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