Re: [BUGS] Optimization disaster - Mailing list pgsql-novice

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/
---------------------------------------------------------------------