Re: [BUGS] Optimization disaster

On Thu, 2002-10-17 at 02:49, Dietrich wrote:
>
> The following Query needs about 0,2 seconds with SEQ_SCAN set to OFF
> and 150 seconds with SEQ_SCAN set to ON:
>
> EXPLAIN
> SELECT DISTINCT location_elem_id, locationseason_id, valid_from ,
> valid_until, startday_exclude FROM bc_quicktravel
> WHERE valid_until >= '2002-10-17' AND valid_from <= '2003-02-13' AND
> attrib_id < 0
> AND location_elem_id IN (SELECT elem_id FROM bc_catalog_x_elem
> WHERE catalog_id IN
> (186827,54786,186724,186829,73115,186831,186726,798429,186833,1368557,186728,186835,186837,186730,1821382,75083,297219,186732,63533,186781,75078,186736,902810,129859,1178799,186750,75281,1387815,54786,1368557,297219,1178799,936688,55706,132578,408097,132576,136852,160655,160657,160653,160665,73110,73115,1821382,1476580,1309559,1303367,1202899,1179539,1106603,1140768,950925,798429,902810,466934,334726,288050,197983,187203,186781,186783,186789,0,3011678,68214,54686,68414,68419)
> AND active_from_time < '2002-10-16 23:59:59 +0200'
> AND active_until_time > '2002-10-16 23:59:59 +0200'
> AND elem_active_and_released = 't'
> AND elem_type = 'location')
> AND (package_elem_id <= 0
> OR package_elem_id IN (SELECT elem_id FROM bc_catalog_x_elem
> WHERE catalog_id IN
> (186827,54786,186724,186829,73115,186831,186726,798429,186833,1368557,186728,186835,186837,186730,1821382,75083,297219,186732,63533,186781,75078,186736,902810,129859,1178799,186750,75281,1387815,54786,1368557,297219,1178799,936688,55706,132578,408097,132576,136852,160655,160657,160653,160665,73110,73115,1821382,1476580,1309559,1303367,1202899,1179539,1106603,1140768,950925,798429,902810,466934,334726,288050,197983,187203,186781,186783,186789,0,3011678,68214,54686,68414,68419)
> AND active_from_time < '2002-10-16 23:59:59 +0200'
> AND active_until_time > '2002-10-16 23:59:59 +0200'
> AND elem_active_and_released = 't'
> AND elem_type = 'travelpack')
> )
> AND valid_until > '2002-10-16 23:59:59 +0200'
> AND package_elem_id < 0
> ORDER BY location_elem_id, valid_from

Have you tried using ... WHERE EXISTS (SELECT... ) rather than WHERE IN
( SELECT ... )

IN(... ) is often not efficient in PostgreSQL.

Regards,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for free with http://survey.net.nz/
---------------------------------------------------------------------