Re: Help request to improve function performance

W dniu 22 kwietnia 2009 23:47 użytkownik Seref Arikan <
serefarikan(at)kurumsalteknoloji(dot)com> napisał:

> Hi Filip,
> First of all: thanks a lot for your kind response. Here is the create
> script for my schema:
>
> CREATE TABLE "app"."archetype_data" (
> "id" BIGINT NOT NULL,
> "context_id" VARCHAR(1000),
> "archetype_name" VARCHAR(1000),
> "archetype_path" VARCHAR(1000),
> "name" VARCHAR(1000),
> "value_string" VARCHAR(1000),
> "value_int" BIGINT,
> "value_double" DOUBLE PRECISION,
> "session_id" VARCHAR(1000),
> "instance_index" INTEGER,
> CONSTRAINT "archetype_data_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
> Now, regarding your feedback, here are the points, hoping that you can give
> me feedback, and bring me up to speed in the topic, for I've been in the
> upper layers of the software world for so long :)

Yes that's easy to observe; but Java and Postgres can go together, I assure
you :)

> Please forgive me for further questions:

come on, nothing to forgive, if I did not like answering questions I would
not read this at all.

>
>
> Are you telling me that due to name resolution process, my use of variable
> and column names for context_id and session_id are causing problems?

that's what I'm telling, doesn't I?

> I'll change variable names into names which would be obviously different
> from column names.

>
> I used the temp table to speed up the following selects, since the actual
> table has more than 9 million rows. after creating the temp table, I am
> selecting from 50K rows. Am I wrong about the performance gain here? What
> would you suggest instead?

Select once. ONCE. Not to temp table, just to a implicit cursor [like here:
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING]
. You can use ORDER BY session_id , so in ONE pass you will be able to
do
all needed computations. ( you will have block of records belonging to one
session_id, then another block for next session_id and so on).

Regarding performance: if you create index on (context_id, session_id) this
query will be fast.

>
> Temp tables forced me to use execute, after hitting a known problem, also
> expressed in the faq, is this what you're talking about?

yes that's it, but as I sad before - you really can get rid of temporary
tables here.

>
>
> I will be investing serious time into postgresql from now on, and I hope
> you can give me couple of useful hints, to ease my way forward :) Looking at
> the schema, can you think of a better way to send this result set to a java
> based app?

Better way to query or better schema?

If you mean better way to query - I would just select all rows and combine
them in upper layer. ARRAYs are not so cute for me.

If you mean better schema... That's the hardest question, as other guys
suggested. You will have to decide what to use. I know that some ORM for
Java (Hibernate, namely) produce quite decent database schemas. NOT E-A-V
model. Maybe this will suit you.

good luck!

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/