Re: strange query filter problems

Yes, explain analyze looks like this:

EXPLAIN
ANALYZE
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) (actual
time=150.274..331.782 rows=190 loops=1)"
"  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) (actual time=104.465..208.185 rows=14417
loops=1)"

"        Filter: (date >= 20050101)"
"  ->  Sort  (cost=6.02..6.03 rows=1 width=75) (actual
time=34.693..40.956 rows=1703 loops=1)"
"        Sort Key: t.sskjema_pkey"
"        ->  Index Scan using speciesix on tskjema t  (cost=0.00..6.01
rows=1 width=75) (actual time=0.140..24.594 rows=1703 loops=1)"

"              Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND
(species ~<~ 'TAGGMAKRELM'::bpchar))"
"              Filter: (species ~~ 'TAGGMAKRELL%'::text)"
"Total runtime: 333.158 ms"



EXPLAIN
ANALYZE
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) (actual
time=1260.988..252110.934 rows=150 loops=1)"
"  Join Filter: ("inner".sskjema_pkey = "outer".sskjema_pkey)"
"  ->  Index Scan using speciesix on tskjema t  (cost=0.00..6.01
rows=1 width=75) (actual time=0.256..50.875 rows=1703 loops=1)"

"        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) (actual time=0.026..76.451 rows=14340
loops=1703)"

"        Index Cond: ((date >= 20050101) AND (date <= 20051231))"
"Total runtime: 252111.940 ms"


Jonas:))

On 4/19/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Wed, Apr 19, 2006 at 01:27:45PM +0200, Jonas Henriksen wrote:
> > 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:
>
> Can we see an EXPLAIN ANALYZE of the two queries, so we can see what's
> actually happening? My guess is that in the second case, the index scan
> on sskjema matches more rows than it expects...
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to litigate.
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFERiCRIB7bNG8LQkwRAq+MAJ4rhGLzU1sYszrT7DUWzPH2+bjVzwCfS1ne
> 5y7A3WhI4PqfDaulFB2hPvc=
> =N5EG
> -----END PGP SIGNATURE-----
>
>
>