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.dekervel@xxxxxxxxxxxxxx> 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.dekervel@xxxxxxxxxxxxxx ========================= 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 =========================
