Re: "append" takes a lot of time in a query

Hello,

Small update on this problem:

Wouter Verhelst came to help debugging, and he determined that the 2 seconds
were spent planning the query and not executing the query. (executing the
query is quick as seen in the query plan).

To avoid replanning this query all the time, Wouter suggest replacing the
query with a stored procedure (so that the query plan was saved). We did a
proof of concept, and it seemed to work very well.

Another approach would be caching of prepared statements, but i found no
implementations of this on the net.

We now still need to find a way to hook the stored procs in our O-R mapper:
generating them the first time a query is done (fairly easy), and
making "select procname(param1,param2)" behave like a normal query.

We tried a stored procedure returning a cursor and this seemed to work, but
we'd like to avoid this as, to use cursors, we need to change the core logic
of our system that decides whether to use transactions and cursors (now the
system does not create a cursor if it does not expect too many rows coming
back and so on, and no transaction if no "update" or "insert" queries have to
be done).

So i'll look further to see if i can make "select foo()" behave exactly like a
normal query.

Another thing we saw was this: on a project where the query generated a
451-line query plan, the query took 30 milliseconds. On a project where the
same query generated a 1051-line query plan (more tables inheriting
the "part" table), the query took 2 seconds. Something of exponential
complexity in the query planner ?

greetings,
Frank

On Friday 16 May 2008 17:59:37 Frank Dekervel wrote:
> Hello,
>
> Thanks for the explanation. You were right, i misread the query plan.
> But the strange thing is this query really takes a long time (see below),
> contrary to what the query plan indicates. This makes me believe we are
> doing something very wrong...
>
> xxx => select now(); SELECT * FROM (part LEFT OUTER JOIN part_lang ON
> part.id = part_lang.id) WHERE part.parent= 49110; select now(); now
> -------------------------------
> 2008-05-16 17:51:15.525056+02
> (1 row)
>
> parent | id | dirindex | permissions | sequencenumber | partname | lang |
> id | online
> --------+----+----------+-------------+----------------+----------+------+-
>---+-------- (0 rows)
>
> now
> -------------------------------
> 2008-05-16 17:51:17.179043+02
> (1 row)
>
> As for postgresql inherited tables: we are moving to joined inheritance
> already, but we still have a lot of "inherited tables" implementations. It
> is the first time we see this kind of problem ...
>
> I'm the original e-mail for reference.
>
> thanks already !
>
> greetings,
> Frank
>
> On Friday 09 May 2008 16:12:46 Tom Lane wrote:
> > Frank Dekervel <frank(dot)dekervel(at)smartlounge(dot)be> writes:
> > > this turns this query in an append of a whole lot of seq scan/ index
> > > scan's. These scans are predictably quick, but the "append" takes 5
> > > seconds (and the numbers of the scans do not add up to the append
> > > actual time)
> >
> > It says 5 milliseconds, not 5 seconds.
> >
> > > Does someone have an explanation / possible solution for this
> > > performance ?
> >
> > Rethink your schema --- this is pushing the inheritance feature far
> > beyond what it's designed to do.
> >
> > regards, tom lane

--

=========================
Frank Dekervel
frank(dot)dekervel(at)smartlounge(dot)be
=========================
Smartlounge
JP Minckelersstraat 78
3000 Leuven
phone:+32 16 311 413
fax:+32 16 311 410
mobile:+32 473 943 421
=========================
http://www.smartlounge.be
=========================