Search Postgresql Archives

select vs cursor/fetch speed disparity

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

 



Hi folks,

I have a strange disparity between a query that is run as a
straight select and the same query via a cursor.  I hope I can
jog someone's memory with the description as I have been unable
to create a sanitized and/or reduced data set & schema that will
reproduce this ... so far. :-(

Running on Ubuntu 10.04 LTS fully updated, PG 8.4.8 and the machine
is no slouch.

I have the following tables and select that express the 'shape' of
the query while they don't actually produce the problem (names and
faces changed to protect the innocent):

===================================================================
create table parent (
    uid         serial8 NOT NULL primary key,
    bits        int8 NOT NULL,
    status      integer NOT NULL
);

create table subs (
    uid         serial8 NOT NULL primary key,
    bits        int8 NOT NULL,
    parent      int8 NOT NULL,
    name        varchar(127) NOT NULL
);

select p.uid, p.status
  from parent p
  where (p.bits & 1) = 0 and
        (p.status in ( 5,8,9,10,11,14)) and
        (p.uid in (select s.parent
                     from subs s
                     where (s.bits & 1) = 0 and
                            s.parent != -1  and
                            lower(s.name) like lower('%xyz%')
                  )
        )
  order by p.uid desc;
===================================================================

(The tables above represent a much reduced table 'width' as they have
many more fields in our DB.)

When I run the query above (which is actually machine generated and
identical to the one causing the issue) on our data-set as a simple
'select' the query takes ~75ms according to \timing.

When I run the following sequence:
    start transaction;
    declare xyz cursor for (the above select)
    fetch xyz;
    rollback;

the 'fetch' takes ~47.3 seconds (i.e. ~47300ms).

In our system the 'parent' table only has ~11k rows and the 'subs'
table only has ~60k rows.

One note that may be important is that the PG backend process that
is running the fetch pegs the CPU it is running on at 100% during
the entire running time for the operation.  (The machine has dual
quad core Opterons & 32GB of RAM.)

I sure hope this reminds someone of some problem I wasn't able to
find in the archives.  In the meantime I will be working on a test
case that reproduces the problem.

TIA.

Bosco.

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux