Skip site navigation (1) Skip section navigation (2)

limiting results makes the query slower

From: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
To: psql performance list <pgsql-performance(at)postgresql(dot)org>
Subject: limiting results makes the query slower
Date: 2009-08-21 01:50:04
Message-ID: 3073cc9b0908201850n4f5f6e84qa574a6312f61a436@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

in a web app we have a query that we want to show in limited results
at a time, this one executes in 10 seconds if i use limit but executes
in 300ms if i remove it.
why is that happening? the query is using and index for avoiding the
sort so the nestloop should go only for the first 20 records on
tgen_persona, no?
below some more info

postgresql 8.3.7
ram 32GB
shared_buffers 8GB
work_mem 8MB

tgen_persona has 185732 records and tcom_invitacion is a partitioned
(by dates: 1 month every partition) table and has more than 29million
records in the partitions

explain analyze here: http://explain.depesz.com/s/B4

the situation improves if i disable nestloops, explain analyze with
nestloop off here: http://explain.depesz.com/s/Jv

select Per.razon_social as MAIL,inv.cata_esta_calificacion,
       inv.observa_calificacion,
       to_char(inv.fech_crea,'YYYY:MM:DD') as fech_crea,
       case when (( select cod_estado FROM TPRO_PROVEEDOR
                     WHERE id_proveedor = (select max(a.id_proveedor)
                                             from tpro_proveedor a
                                            where persona_id = Inv.persona_id )
                  )='Habilitado')
            then 'Habilitado'
            else 'Deshabilitado'
       end as empresa_id
  from tgen_persona Per, tcom_invitacion Inv
 where Per.persona_id = Inv.persona_id
   and inv.id_soli_compra = '60505'
 ORDER BY Per.razon_social asc limit 20 offset 0


-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-08-21 01:58:41
Subject: Re: number of rows estimation for bit-AND operation
Previous:From: Scott CareyDate: 2009-08-21 01:33:56
Subject: Re: improving my query plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group