[SQL] Speed depending of Join Order.
I will explain my question usin an example. I have two tables as
follows:
Table "public.image_mode"
Column| Type | Modifiers
-+---+---
mis_id | character(5) | not null
ins_id | character(5) | not null
img_id | character(25) | not null
mod_mis_id | character(5) | not null
mod_ins_id | character(5) | not null
mod_id | character(5) | not null
mod_valueid | character(5) | not null
Indexes: pk_imgmode primary key btree (mis_id, ins_id, img_id,
mod_mis_id, mod_ins_id, mod_id, mod_valueid),
image_mode_fk_image btree (mis_id, ins_id, img_id),
image_mode_fk_mode btree (mod_mis_id, mod_ins_id, mod_id,
mod_valueid)
Table "public.mode"
Column | Type | Modifiers
-++---
mis_id | character(5) | not null
ins_id | character(5) | not null
mod_id | character(5) | not null
mod_valueid | character(5) | not null
mod_name| character varying(50) | not null
mod_value | character varying(25) | not null
vmod_valuenr| double precision |
vmod_valueunits | character varying(25) |
vmod_obs| character varying(255) |
Indexes: pk_mode primary key btree (mis_id, ins_id, mod_id, mod_valueid)
Ten I perform the same search in two different ways:
SELECT mod.mod_id, mod.mod_value
FROM image_mode imod, mode mod
WHERE imod.mod_mis_id = mod.mis_id
AND imod.mod_ins_id = mod.ins_id
AND imod.mod_id = mod.mod_id
AND imod.mod_valueid= mod.mod_valueid
AND imod.mis_id='XXX'
AND imod.ins_id='YYY'
AND imod.img_id='ZZZ';
SELECT mod.mod_id, mod.mod_value
FROM image_mode imod, mode mod
WHERE mod.mis_id = imod.mod_mis_id
AND mod.ins_id = imod.mod_ins_id
AND mod.mod_id = imod.mod_id
AND mod.mod_valueid= imod.mod_valueid
AND imod.mis_id='XXX'
AND imod.ins_id='YYY'
AND imod.img_id='ZZZ';
Note that the only difference is the order of the join elements. Using
version 7.2.2, which I have been using untill now, the time expended in
both of them was the same, using the right indexes. However, using
version 7.3.1 which I have instaled recently, the results of the explain
are the following:
Case 1:
Merge Join (cost=1.79..1.92 rows=1 width=79) (actual
time=404.29..4109.78 rows=2 loops=1)
Merge Cond: (("outer".mod_mis_id = "inner".mis_id) AND
("outer".mod_ins_id = "inner".ins_id) AND ("outer".mod_id =
"inner".mod_id) AND ("outer".mod_valueid = "inner".mod_valueid))
-> Index Scan using image_mode_fk_mode on image_mode imod
(cost=0.00..606979.14 rows=1 width=36) (actual time=403.42..4108.67
rows=2 loops=1)
Filter: ((mis_id = 'IUE'::bpchar) AND (ins_id = 'LWP'::bpchar)
AND (img_id = 'HL28915'::bpchar))
-> Sort (cost=1.79..1.85 rows=24 width=43) (actual time=0.81..0.81
rows=5 loops=1)
Sort Key: mod.mis_id, mod.ins_id, mod.mod_id, mod.mod_valueid
-> Seq Scan on "mode" mod (cost=0.00..1.24 rows=24 width=43)
(actual time=0.10..0.19 rows=24 loops=1)
Total runtime: 4109.96 msec
Case 2: -
Merge Join (cost=5.69..5.71 rows=1 width=79) (actual time=1.12..1.30
rows=2 loops=1)
Merge Cond: (("outer".mis_id = "inner".mod_mis_id) AND
("outer".ins_id = "inner".mod_ins_id) AND ("outer".mod_id =
"inner".mod_id) AND ("outer".mod_valueid = "inner".mod_valueid))
-> Index Scan using pk_mode on "mode" mod (cost=0.00..6.08 rows=24
width=43) (actual time=0.27..0.30 rows=5 loops=1)
-> Sort (cost=5.69..5.70 rows=1 width=36) (actual time=0.81..0.81
rows=2 loops=1)
Sort Key: imod.mod_mis_id, imod.mod_ins_id, imod.mod_id,
imod.mod_valueid
-> Index Scan using image_mode_fk_image on image_mode imod
(cost=0.00..5.68 rows=1 width=36) (actual time=0.58..0.61 rows=2
loops=1)
Index Cond: ((mis_id = 'IUE'::bpchar) AND (ins_id =
'LWP'::bpchar) AND (img_id = 'HL28915'::bpchar))
Total runtime: 1.45 msec
As you can see, there is a great differece in the time it takes to
execute each of them since a sequential scan is performed in Case 1
instead an Index scan. I have run vacuum analyze so I am sure this is
not the problem.
Thank you very much in advance,
Raul Gutierrez
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] [OT] Unsubscribe does not work
Hi, once I subscribed to this list I've got the information how to subscribe: If you ever want to remove yourself from this mailing list, send the following command in email to [EMAIL PROTECTED]: approve unsubscribe Andreas Tille <[EMAIL PROTECTED]> Well, I did so but I'm still reciving messages from this list (as well as from PostgreSQL-general <[EMAIL PROTECTED]> but I wanted to bother the lower volume list at first.) I'll be on vacation for a longer period and do not want my mailbox flooded ... Any help Andreas. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] performance question
Hello,
I have a table with some 2.2 million rows on a Pentium4, 1.8GHz with 512
MB RAM.
Some queries I launch take quite a long time, and I'm wondering whether
this is normal,or whether I can get better performance somehow.
As an example, I have a field which is in char(2), with 23 different
possible values. When I launch a "select * from table where field = 'xx'"
this takes a very long time (some 230194.10 msec, i.e. almost 4 minutes,
according to "explain analyze"). I've tried indexing on this column and
vacuuming ("vacuum analyze"), but this doesn't change anything. "explain
select" always gives me a sequential scan. The correlation value in
pg_stats for this column is 0.0583268, so a seq scan is probably cheaper
than index scan.
Now maybe this is just the way it is and there is no possibility to
enhance performance, but if someone has some tips on what I might try,
they are very welcome !
One question I asked myself is whether the use of char(2) is the best
option. The column (and most others in the table) contains codes that
designate different characteristics (for ex. in a column 'sex' one would
find '1'=male, '2'=female).
Moritz
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] returning setof in plpgsql
I have a function that I want to return setof a table in plpgsql.
Here is what I have:
CREATE FUNCTION lookup_account(varchar(32)) RETURNS SETOF accounts AS '
DECLARE
aname ALIAS FOR $1;
rec RECORD;
BEGIN
select into rec * from accounts where accountname = aname;
return rec;
END;'
LANGUAGE 'plpgsql';
This seems to hang when I attempt to select it using:
select accountid(
lookup_account('some account')),
accountname(lookup_account('some account')),
type(lookup_account('some account')),
balance(lookup_account('some account'));
Does anyone see a problem w/ my approach??
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [OT] Unsubscribe does not work
On Tue, Jan 21, 2003 at 14:23:02 +0100, Andreas Tille <[EMAIL PROTECTED]> wrote: > Hi, > > once I subscribed to this list I've got the information how to > subscribe: > >If you ever want to remove yourself from this mailing list, >send the following command in email to >[EMAIL PROTECTED]: > >approve unsubscribe Andreas Tille <[EMAIL PROTECTED]> > > Well, I did so but I'm still reciving messages from this list > (as well as from >PostgreSQL-general <[EMAIL PROTECTED]> > but I wanted to bother the lower volume list at first.) What response did you get to your message? I usually use [EMAIL PROTECTED] as the address to send mailing list commands to, though it is possible the address you tried works as well. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [OT] Unsubscribe does not work
On Tue, 21 Jan 2003, Bruno Wolff III wrote: > What response did you get to your message? No response at all. > I usually use [EMAIL PROTECTED] as the address to send mailing > list commands to, though it is possible the address you tried works > as well. I normally send those command to majordomo as usual for mailing lists, but if I get those explicite advise ... Next try: mailx -s unsubscribe [EMAIL PROTECTED] <<... unsubscribe ... Let's see what happens ... Andreas. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] returning setof in plpgsql
On Tuesday 21 Jan 2003 10:40 am, David Durst wrote:
> I have a function that I want to return setof a table in plpgsql.
>
> Here is what I have:
>
> CREATE FUNCTION lookup_account(varchar(32)) RETURNS SETOF accounts AS '
> DECLARE
> aname ALIAS FOR $1;
> rec RECORD;
> BEGIN
> select into rec * from accounts where accountname = aname;
> return rec;
> END;'
> LANGUAGE 'plpgsql';
>
> This seems to hang when I attempt to select it using:
>
> select accountid(
> lookup_account('some account')),
> accountname(lookup_account('some account')),
> type(lookup_account('some account')),
> balance(lookup_account('some account'));
>
> Does anyone see a problem w/ my approach??
Hi David,
I've never done this but I seem to remember seeing something about this
recently.
Firstly, I think you need 7.3.1 to do this.
You then have to create a 'type' as being a set of your table. You then
define the function as returning that type.
Sorry I can't be more specific, but as I said, I've never done it.
Gary
>
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [OT] Unsubscribe does not work
Bruno Wolff III <[EMAIL PROTECTED]> writes: > I usually use [EMAIL PROTECTED] as the address to send mailing > list commands to, though it is possible the address you tried works > as well. majordomo worked the last time I tried it. Rather than unsubscribing (and losing all your settings) I'd suggest selecting the "nomail" subscription mode while you're on vacation. Sending "help" and "help set" to majordomo should provide the details. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Speed depending of Join Order.
=?iso-8859-1?Q?Ra=FAl=20Guti=E9rrez=20S=E1nchez?= <[EMAIL PROTECTED]> writes: > Note that the only difference is the order of the join elements. Using > version 7.2.2, which I have been using untill now, the time expended in > both of them was the same, using the right indexes. However, using > version 7.3.1 which I have instaled recently, the results of the explain > are the following: That seems like a bug. Are the tables small enough that you could send me a pg_dump of them? I doubt I can reproduce this without the specific test case. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] returning setof in plpgsql
"David Durst" <[EMAIL PROTECTED]> writes:
> CREATE FUNCTION lookup_account(varchar(32)) RETURNS SETOF accounts AS '
> DECLARE
> aname ALIAS FOR $1;
> rec RECORD;
> BEGIN
> select into rec * from accounts where accountname = aname;
> return rec;
> END;'
> LANGUAGE 'plpgsql';
As written, this function can only return a single row (so you hardly
need SETOF). If you intend that it be able to return multiple rows
when accountname is not unique, then you'll need a loop and RETURN NEXT
commands. It'd probably be less tedious to use a SQL-language function:
CREATE FUNCTION lookup_account(varchar(32)) RETURNS SETOF accounts AS '
select * from accounts where accountname = $1'
language sql;
> This seems to hang when I attempt to select it using:
> select accountid(
> lookup_account('some account')),
> accountname(lookup_account('some account')),
> type(lookup_account('some account')),
> balance(lookup_account('some account'));
It works for me (in 7.3), but in any case that's a bad approach: you're
invoking the function four times, independently. Better is
select accountid,accountname,type,balance
from lookup_account('some account');
(again, this syntax requires 7.3)
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] help
help ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Oops - help error.
Sorry .. My apologies I clicked too quick too early .. Please disregard my help request to the wrong address .. Promise it won't happen again .. Thanks for everything Best Regards Rudi. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
