Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Query is :
SELECT distinct m.id,coalesce(m.givenname,''),
       coalesce(m.midname,''),
       m.surname from marinerstates ms,vessels vsl,mariner m 
WHERE  m.id=ms.marinerid and ms.vslid=vsl.id 
AND    ms.state='Active' and coalesce(ms.endtime,now())::date >= '2006-07-15'
AND    ms.starttime::date <= '2007-01-11'  AND
       m.marinertype='Mariner'  and m.id 
NOT IN (SELECT distinct mold.id
        FROM marinerstates msold,
        vessels vslold,
        mariner mold  
        WHERE mold.id=msold.marinerid 
        AND  msold.vslid=vslold.id
        AND msold.state='Active' 
        AND coalesce(msold.endtime,now())::date >= '2006-07-15' 
        AND msold.starttime::date <= '2007-01-11' 
        AND EXISTS (SELECT 1 
                    FROM marinerstates msold2 
                    WHERE msold2.marinerid=msold.marinerid
                    AND msold2.state='Active' 
                    AND msold2.id <> msold.id 
                    AND msold2.starttime<msold.starttime
                    AND (msold.starttime-msold2.endtime)<='18 months')
                    AND mold.marinertype='Mariner' ) 
       ORDER BY m.surname,coalesce(m.givenname,'')
      ,coalesce(m.midname,'');   

i get the following execution times: (with \timing) 
FBSD_DEV : query : 240.419 ms
LINUX_PROD : query : 219.568 ms
FBSD_TEST : query :  2285.509 ms
LINUX_TEST : query : 5788.988 ms

Re writing the query in the "NOT EXIST" variation like:

SELECT distinct m.id,coalesce(m.givenname,''),coalesce(m.midname,''),m.surname from marinerstates ms,vessels vsl,mariner m where 
m.id=ms.marinerid and ms.vslid=vsl.id and ms.state='Active' and coalesce(ms.endtime,now())::date >= '2006-07-15' and 
ms.starttime::date <= '2007-01-11'  and m.marinertype='Mariner'  and NOT EXISTS 
   (SELECT distinct mold.id from marinerstates msold,vessels vslold,mariner mold  where mold.id=msold.marinerid and msold.vslid=vslold.id and 
   msold.state='Active' and coalesce(msold.endtime,now())::date >= '2006-07-15' and msold.starttime::date <= '2007-01-11' and 
   exists (select 1 from marinerstates msold2 where msold2.marinerid=msold.marinerid and msold2.state='Active' and msold2.id <> msold.id and 
              msold2.starttime<msold.starttime AND (msold.starttime-msold2.endtime)<='18 months')  
   and mold.marinertype='Mariner' AND mold.id=m.id) 
order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'');
gives:

FBSD_DEV : query : 154.000 ms
LINUX_PROD : query : 153.408 ms
FBSD_TEST : query :  137.000 ms
LINUX_TEST : query : 404.000 ms


Well, on the Release Notes on the PostgreSQL-8.4 Documentation, the developers recommend to use NOT EXISTS 
instead NOT IN, because the first clause has a better performance. So, you can use it on that way.

Other questions?
- Do you have a partial index on marinerstates.marinerid where this condition is accomplished?
- Do you have a index on mariner.id?
- Can you provide a explain of these queries on the PostgreSQL-9.0 machines?

Regards


Ing. Marcos LuÃs OrtÃz Valmaseda
Linux User # 418229 && PostgreSQL DBA
Centro de TecnologÃas GestiÃn de Datos (DATEC)
http://postgresql.uci.cu
http://www.postgresql.org
http://it.toolbox.com/blogs/sql-apprentice

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux