Thanks a lot for your kind help. Selecting only once did the trick. Dropping to 2 seconds for select instead of 50 IS an improvement indeed :)
Indexes on columns already existed, and just out of curiosity I've tested char columns instead of varchars, with no significant positive changes. Eliminating the loop made all the difference.
Next thing to check out is the partitioning options.
Kind regards.
2009/4/23 Filip Rembiałkowski <plk.zuber@xxxxxxxxx>
W dniu 22 kwietnia 2009 23:47 użytkownik Seref Arikan <serefarikan@xxxxxxxxxxxxxxxxxxxxx> 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!
