Re: [PERFORM] How to read query plan

[Miroslav Šulc]

Tom Lane wrote:
...
I think the reason this is popping to the top of the runtime is that the
joins are so wide (an average of ~85 columns in a join tuple according
to the numbers above).  Because there are lots of variable-width columns
involved, most of the time the fast path for field access doesn't apply
and we end up going to nocachegetattr --- which itself is going to be
slow because it has to scan over so many columns.  So the cost is
roughly O(N^2) in the number of columns.
 

As there are a lot of varchar(1) in the AdDevicesSites table, wouldn't 
be helpful to change them to char(1)? Would it solve the variable-width 
problem at least for some fields and speed the query up?

As a short-term hack, you might be able to improve matters if you can
reorder your LEFT JOINs to have the minimum number of columns
propagating up from the earlier join steps.  In other words make the
later joins add more columns than the earlier, as much as you can.
 

That will be hard as the main table which contains most of the fields is 
LEFT JOINed with the others. I'll look at it if I find some way to 
improve it.

I'm not sure whether I understand the process of performing the plan but 
I imagine that the data from AdDevicesSites are retrieved only once when 
they are loaded and maybe stored in memory. Are the columns stored in 
the order they are in the SQL command? If so, wouldn't it be useful to 
move all varchar fields at the end of the SELECT query? I'm just 
guessing because I don't know at all how a database server is 
implemented and what it really does.

..
			regards, tom lane
 

Miroslav
begin:vcard
fn;quoted-printable:Miroslav =C5=A0ulc
n;quoted-printable:=C5=A0ulc;Miroslav
org:StartNet s.r.o.
adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika
email;internet:[EMAIL PROTECTED]
title:CEO
tel;work:+420 257 225 602
tel;cell:+420 603 711 413
x-mozilla-html:TRUE
url:http://www.startnet.cz
version:2.1
end:vcard
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings