Re: planer picks a bad plan (seq-scan instead of index)

Thomas H. wrote:
> hi list.
>
> as soon as i left-join an additional table, the query takes 24sec
> instead of 0.2sec, although the added fields have no impact on the
> resultset:
>
> --------------------
> SELECT * FROM shop.dvds
> LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
> WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like
> '%superman re%' OR lower(dvd_edition) LIKE '%superman re%')
> --------------------
> Hash Left Join  (cost=8402.16..10733.16 rows=39900 width=1276) (actual
> time=260.712..260.722 rows=2 loops=1)
>  Hash Cond: (dvds.dvd_mov_id = movies.mov_id)
>  Filter: ((lower((movies.mov_name)::text) ~~ '%superman re%'::text) OR
> (lower((dvds.dvd_edition)::text) ~~ '%superman re%'::text))
>  ->  Seq Scan on dvds  (cost=0.00..1292.00 rows=39900 width=1062)
> (actual time=0.036..23.594 rows=20866 loops=1)
>  ->  Hash  (cost=8194.93..8194.93 rows=82893 width=214) (actual
> time=168.121..168.121 rows=37417 loops=1)
>        ->  Seq Scan on movies  (cost=0.00..8194.93 rows=82893 width=214)
> (actual time=0.024..131.401 rows=37417 loops=1)
> Total runtime: 264.193 ms
> 2 rows fetched
> --------------------

That's a pretty bad plan already, considering it does two seq-scans. I'm
pretty sure you can get that query to return in something close to 1ms.

Do you have indexes on any of dvds.dvd_mov_id, movies.mov_id,
lower(mov_name), lower(dvd_edition) or lower(dvd_name)?

I think that'd help.

If you already do have those indices, you may be running out of memory;
check for how much memory your postgres is set, the defaults are rather
modest.

> now, an additional table (containing 600k records) is added through a
> left join. all the sudden the query takes 24sec. although there are
> indices on both dvds.dvd_ean and data_soundmedia.sm_info_ean, the planer
> does not make use of the indices but rather chooses to do 2 seq-scans.
>
> --------------------
> SELECT * FROM shop.dvds
> LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
> LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean

Make sure you have indexes on both sm_info_ean and dvd_ean.

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //