[SQL] Performance on large functions
Folks, I have this function that adds 100-900 rows to a table and then unpdates them 12 times using data pulled from all over the database. I've increased the pgsql buffer, the sort memory, and wal_files significantly (2048, 1024 and 16) as well as adding a few relevant indexes. However, this function does not seem to improve in response time by more than 10%, no matter how much resources I give postgresql. While the 35 seconds it takes on an empty system isn't a problem, towards the end of the day with a full slate of users it's taking several minutes -- an unacceptable delay at this stage. I can't help but feel that, because functions wrap everything in a transaction, some sort of tinkering with the xlog settings/performance is called for ... but I haven't found any docs on this. Or maybe I'm just being choked by the speed of disk access? Can anyone point me in the right direction, or should I be posting this to a different list? -Josh Berkus P.S. Postgres 7.1.2 running on SuSE Linux 7.2 on a Celeron 500/128mb RAM/IDE HDD. __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Date/Time FAQ posted
Folks, The PostgreSQL Date/Time/Interval FAQ has been posted to techdocs: http://techdocs.postgresql.org/techdocs/faqdatesintervals.php Any expansions of the FAQ are welcome, particularly: 1. An explanation of TIMEZONE manipulation. 2. Replacement DATEADD and DATEDIFF functions for users porting from MS SQL Server or SyBase. Grazie! -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] WHERE on an alias
How do you do a subselect in the from clause? Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a); >>ERROR: Attribute 'dsum' not found >> > >>Why can we GROUP BY on an alias but not do a WHERE on an alias? >> > > Because WHERE is computed before the select's output list is. > > Strictly speaking you shouldn't be able to GROUP on an alias either (the > SQL spec doesn't allow it). We accept that for historical reasons only, > ie, our interpretation of GROUP used to be wrong and we didn't want to > break applications that relied on the wrong interpretation. > > Note that writing a GROUP on an alias does *not* mean the alias is only > computed once. It saves no computation, only writing out the expression > twice. > > >>I have a subselect that >>explain shows is being run twice if I have to put it in the WHERE clause. >> > > Possibly you could restructure your query into something with a > subselect in the FROM clause? > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(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] WHERE on an alias
Josh Berkus wrote: > Joseph, > > >>How do you do a subselect in the from clause? >> > > Assuming that you are using 7.1.0 or higher: > > SELECT tbla.a, tbla.b, total_b > FROM tbla, >(SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot > WHERE tbla.b = b_tot.b > OK my query was like: select u.field1, ... , d.field1, ... (select ml.field from ml WHERE ml.key = u.key order by ml.keyfield desc limit 1) as lastml FROM utable u, dtable d, WHERE u.key = d.key and ... and (sublectect again) = 2; OK when I tried to convert the query: select u.field1, ... , d.field1, ..., mll.lastml FROM utable u, dtable d,(select ml.field as lastml from ml WHERE ml.key = u.key order by ml.keyfield desc limit 1) mll WHERE u.key = d.key and ... and lastml = 2; I got: ERROR: Relation 'u' does not exist The key fields in the subselect must match up with the key fields in the outside query. I'm afraid this would indicate that they would not match up (even if I didn't use short aliases for the tables) and the result of the subselect would be meaningless. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] WHERE on an alias
Joseph, > How do you do a subselect in the from clause? Assuming that you are using 7.1.0 or higher: SELECT tbla.a, tbla.b, total_b FROM tbla, (SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot WHERE tbla.b = b_tot.b If you're using 7.0 still, you're S.O.L. Time to upgrade ;-) -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] WHERE on an alias
Joseph,
Please take a look at my example again:
> > SELECT tbla.a, tbla.b, total_b
> > FROM tbla,
> >(SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot
> > WHERE tbla.b = b_tot.b
> >
> OK when I tried to convert the query:
> select u.field1, ... , d.field1, ..., mll.lastml FROM utable u,
> dtable d,(select ml.field
> as lastml from ml WHERE ml.key = u.key order by ml.keyfield desc
> limit 1) mll WHERE u.key
> = d.key and ... and lastml = 2;
You need to put the JOIN condition from the subselect ("ml.key = u.key")
OUTSIDE the subselect ("ml.key = mll.key"). You can't reference columns
from the main select inside a subselect except in an EXISTS clause.
This does mean that you're going to need some approach other than the
"limit 1" to limit the rows in your result set. Probably at GROUP BY.
-Josh
P.S. I'm sure you don't mean for your e-mails to come across as
antagonistic when you are asking for help. Can you please be careful of
your phrasing?
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] WHERE on an alias
On Mon, 27 Aug 2001, Joseph Shraibman wrote: > OK my query was like: > select u.field1, ... , d.field1, ... (select ml.field from ml WHERE ml.key = u.key >order > by ml.keyfield desc limit 1) as lastml FROM utable u, dtable d, WHERE u.key = d.key >and > ... and (sublectect again) = 2; > > OK when I tried to convert the query: > select u.field1, ... , d.field1, ..., mll.lastml FROM utable u, dtable d,(select >ml.field > as lastml from ml WHERE ml.key = u.key order by ml.keyfield desc limit 1) mll WHERE >u.key > = d.key and ... and lastml = 2; > > I got: > ERROR: Relation 'u' does not exist > > > The key fields in the subselect must match up with the key fields in the outside >query. > I'm afraid this would indicate that they would not match up (even if I didn't use >short > aliases for the tables) and the result of the subselect would be meaningless. I think you'd want to move the entire query excepting the lastml where condition into a single subselect in the outer from with the lastml condition on the outside: select * from () as blah where lastml=2; However, I don't think this changes the computation that it's doing (simple example explains still show two subquery runs). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] WHERE on an alias
Josh Berkus wrote:
> Joseph,
>
> Please take a look at my example again:
>
>
>>>SELECT tbla.a, tbla.b, total_b
>>>FROM tbla,
>>> (SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot
>>>WHERE tbla.b = b_tot.b
>>>
>>>
>
>>OK when I tried to convert the query:
>>select u.field1, ... , d.field1, ..., mll.lastml FROM utable u,
>>dtable d,(select ml.field
>>as lastml from ml WHERE ml.key = u.key order by ml.keyfield desc
>>limit 1) mll WHERE u.key
>>= d.key and ... and lastml = 2;
>>
>
> You need to put the JOIN condition from the subselect ("ml.key = u.key")
> OUTSIDE the subselect ("ml.key = mll.key"). You can't reference columns
> from the main select inside a subselect except in an EXISTS clause.
Actually I do it all the time, in the select part.
>
> This does mean that you're going to need some approach other than the
> "limit 1" to limit the rows in your result set. Probably at GROUP BY.
Well that is the problem. In my subselect I only want the latest value of ml.field,
which
I get my ordering my ml.keyfield. I don't see how group by could help. I would need
to
do something like: ... WHERE mll.keyfield = (select ml.keyfield from mltable ml where
...
order by ml.keyfield desc limit 1) which would be a subselect again which is what I'm
trying to avoid.
SQL is really annoying.
>
> -Josh
>
> P.S. I'm sure you don't mean for your e-mails to come across as
> antagonistic when you are asking for help. Can you please be careful of
> your phrasing?
I certainly didn't mean to be antagonistic, and looking at the email I sent I don't
see
why you thought it was.
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(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] WHERE on an alias
Stephan Szabo wrote: > > > I think you'd want to move the entire query excepting the lastml where > condition into a single subselect in the outer from with the lastml > condition on the outside: > select * from () as blah where > lastml=2; > > However, I don't think this changes the computation that it's doing > (simple example explains still show two subquery runs). > Yep, that works. I wasn't familiar with how the subselect in the from part works. But as you say an explain still shows it doing the index scan twice. From the postgres docs: A sub-SELECT can appear in the FROM clause. This acts as though its output were created as a temporary table for the duration of this single SELECT command. Note that the sub-SELECT must be surrounded by parentheses, and an alias must be provided for it. So does postgres actually use a temporary table behind the scenses? It appears not. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] WHERE on an alias
Joseph, > Actually I do it all the time, in the select part. Hmm. Frankly, I didn't know that Subselects in the field list were supported, so this is a new one on me. > Well that is the problem. In my subselect I only want the latest > value of ml.field, which > I get my ordering my ml.keyfield. I don't see how group by could > help. I would need to > do something like: ... WHERE mll.keyfield = (select ml.keyfield from > mltable ml where ... > order by ml.keyfield desc limit 1) which would be a subselect again > which is what I'm > trying to avoid. > > SQL is really annoying. If you think SQL is annoying, you should try relational calculus ;-) Think you can provide me a simplified version of your table structure and the results you're trying to get out of your query? I think that your problem is solvable with a little nested subselect, but I'm having a lot of difficulty picturing what we're looking at. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(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] WHERE on an alias
On Mon, 27 Aug 2001, Joseph Shraibman wrote: > Stephan Szabo wrote: > > I think you'd want to move the entire query excepting the lastml where > > condition into a single subselect in the outer from with the lastml > > condition on the outside: > > select * from () as blah where > > lastml=2; > > > > However, I don't think this changes the computation that it's doing > > (simple example explains still show two subquery runs). > > > > Yep, that works. I wasn't familiar with how the subselect in the from part works. >But as > you say an explain still shows it doing the index scan twice. > > From the postgres docs: > > A sub-SELECT can appear in the FROM clause. This acts as though its output >were > created as a temporary table for the duration of this single SELECT command. Note >that the > sub-SELECT must be surrounded by parentheses, and an alias must be provided for it. > > So does postgres actually use a temporary table behind the scenses? It appears not. I think what's happening is that the where condition is being pushed down into the subselect because usually that's the correct optimization, since you'd normally want select col1 from (select col1 from foo) as foo2 where col1=2; to do an index scan on foo. ---(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] WHERE on an alias
Joseph, > select d.field1, d.field2, u.field1, u.field2, (select ml.field from > mltable where ml.key1 > = u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from > utable u, dtable d, > where u.key1 = d.key order by d.somefield limit 25 offset ???; >From the look of it, you want to simply take the utable references out of the subselect. Try: SELECT d.field1, d.field2, u.field1, u.field2, mll.latest FROM utable u, dtable d, (SELECT key1, key2, max(serial) as latest FROM mltable GROUP BY key1, key2) mll WHERE u.key1 = d.key AND u.key1 = mll.key1 and u.key2 = mll.key2 ORDER BY d.somefield LIMIT 25 OFFSET $pageno That should give you the utable and dtable records, plus the last serial value while executing the subselect only once per query call. Give it a spin. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] WHERE on an alias
Except I want ml.field, which is a data field, not a key. So I can't group by it. Bascially the point of the subselect is to get the field value where serial is hightest and the two keys match. Josh Berkus wrote: > Joseph, > > >>select d.field1, d.field2, u.field1, u.field2, (select ml.field from >>mltable where ml.key1 >>= u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from >>utable u, dtable d, >>where u.key1 = d.key order by d.somefield limit 25 offset ???; >> > >>From the look of it, you want to simply take the utable references out > of the subselect. Try: > > SELECT d.field1, d.field2, u.field1, u.field2, mll.latest > FROM utable u, dtable d, > (SELECT key1, key2, max(serial) as latest > FROM mltable > GROUP BY key1, key2) mll > WHERE u.key1 = d.key > AND u.key1 = mll.key1 and u.key2 = mll.key2 > ORDER BY d.somefield > LIMIT 25 OFFSET $pageno > > That should give you the utable and dtable records, plus the last serial > value while executing the subselect only once per query call. Give it a > spin. > > -Josh > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] WHERE on an alias
Actually I think I discovered part of the problem. My subquery did: ORDER BY serial DESC LIMIT 1 where serial happened to be the primary key. The explain showed that it was doing an index search on this serial. So I did the order by on a date field that doesn't have an index, so the query used a different index to do the query. The different index that it used was on the two keys that the mltable shares with the utable, so the query went much faster even though the explain estimated it taking longer. I do a vacuum analyze each night in the cron. More info: the utable is uniqe on two fields. The mltable has these coresponding key fields in it, but there is more than one entry that has the same field combination. I have an index on these because I knew I would want to get the entries by these two fields. The explain for the fast query shows: -> Sort (cost=3106.17..3106.17 rows=1363 width=10) -> Index Scan using mltabke_u_and_p_key on mltable ml (cost=0.00..3035.22 rows=1363 width=10) While the explain for the slow one is: -> Index Scan Backward using mltable_pkey on mltable ml (cost=0.00..28794.49 rows=1363 width=6) I don't know why the planner thought the sort would be so expensive. Stephan Szabo wrote: > On Mon, 27 Aug 2001, Joseph Shraibman wrote: > > >>Stephan Szabo wrote: >> >>>I think you'd want to move the entire query excepting the lastml where >>>condition into a single subselect in the outer from with the lastml >>>condition on the outside: >>> select * from () as blah where >>> lastml=2; >>> >>>However, I don't think this changes the computation that it's doing >>>(simple example explains still show two subquery runs). >>> >>> >>Yep, that works. I wasn't familiar with how the subselect in the from part works. >But as >>you say an explain still shows it doing the index scan twice. >> >> From the postgres docs: >> >> A sub-SELECT can appear in the FROM clause. This acts as though its output >were >>created as a temporary table for the duration of this single SELECT command. Note >that the >>sub-SELECT must be surrounded by parentheses, and an alias must be provided for it. >> >>So does postgres actually use a temporary table behind the scenses? It appears not. >> > > I think what's happening is that the where condition is being pushed down > into the subselect because usually that's the correct optimization, since > you'd normally want > select col1 from (select col1 from foo) as foo2 where col1=2; > to do an index scan on foo. > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] WHERE on an alias
Josh Berkus wrote: > Joseph, > > >>select d.field1, d.field2, u.field1, u.field2, (select ml.field from >>mltable where ml.key1 >>= u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from >>utable u, dtable d, >>where u.key1 = d.key order by d.somefield limit 25 offset ???; >> > >>From the look of it, you want to simply take the utable references out > of the subselect. Try: > > SELECT d.field1, d.field2, u.field1, u.field2, mll.latest > FROM utable u, dtable d, > (SELECT key1, key2, max(serial) as latest > FROM mltable > GROUP BY key1, key2) mll > WHERE u.key1 = d.key > AND u.key1 = mll.key1 and u.key2 = mll.key2 > ORDER BY d.somefield > LIMIT 25 OFFSET $pageno > > That should give you the utable and dtable records, plus the last serial > value while executing the subselect only once per query call. Give it a > spin. > OK I tried a variant on this of doing select ml.field from (sublect) mll WHERE ml.field in(values) and mll.serial = ml.serial. I am now doing two queries, but the second one is a simple index query that returns rather fast. The first, however, is horendous. I have thousands of entries in the utable, and this subquery is getting all thoses thousands of corresponding max(serial) values from the mltable before throwing all but the 25 that this query needs away. I didn't spell out exactly how all my data is aranged so you didn't know this would happend, but otherwise it might have worked. Or maybe postgres should have been smart enough to eliminate the other values before it did the group by > -Josh > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(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] WHERE on an alias
Joseph Shraibman <[EMAIL PROTECTED]> writes: > From the postgres docs: > A sub-SELECT can appear in the FROM clause. This acts as though its output >were > created as a temporary table for the duration of this single SELECT command. Note >that the > sub-SELECT must be surrounded by parentheses, and an alias must be provided for it. > So does postgres actually use a temporary table behind the scenses? No. The point of that part of the docs is that the sub-SELECT's result is (logically) computed independently of the query that it appears in --- thus, you can't have references to tables that aren't part of that sub-SELECT. This is a lot different from subselects in WHERE, the select target list, etc, since they can depend on their context. If you can think of a clearer way of phrasing the docs, let's have it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] WHERE on an alias
Josh Berkus wrote: > Joseph, > > >>Actually I do it all the time, in the select part. >> > > Hmm. Frankly, I didn't know that Subselects in the field list were > supported, so this is a new one on me. > > > >>Well that is the problem. In my subselect I only want the latest >>value of ml.field, which >>I get my ordering my ml.keyfield. I don't see how group by could >>help. I would need to >>do something like: ... WHERE mll.keyfield = (select ml.keyfield from >>mltable ml where ... >>order by ml.keyfield desc limit 1) which would be a subselect again >>which is what I'm >>trying to avoid. >> >>SQL is really annoying. >> > > If you think SQL is annoying, you should try relational calculus ;-) > > Think you can provide me a simplified version of your table structure > and the results you're trying to get out of your query? I think that > your problem is solvable with a little nested subselect, but I'm having > a lot of difficulty picturing what we're looking at. > Basically there is the utable, which has two keys in it. I want to select some rows from the utable. Some columns come from the dtable, which has a key that matches to one of the utable keys, and I want to select the last value from the mltable which has the same two keys as utable and it has a serial value that I use do determine which is the last entry. I also want to use the last value from the mltable in the WHERE clause. So the select is basically select d.field1, d.field2, u.field1, u.field2, (select ml.field from mltable where ml.key1 = u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from utable u, dtable d, where u.key1 = d.key order by d.somefield limit 25 offset ???; The tricky part is the mltable. I don't want to put the whole subselect into the where clause because the mltable lookup is the most expensive part. And I need to have the limit and offset be done by the entries in the utable/dtable. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(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] WHERE on an alias
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Bascially the point of the subselect is to get the field value where > serial is hightest and the two keys match. If you don't mind nonstandard SQL, perhaps something involving SELECT DISTINCT ON would solve your problem (look at the weather-report example on the SELECT reference page for inspiration). But I'm still pretty fuzzy on what the table layout is and why this computation makes any sense. Maybe the real answer is to back up a few steps and reconsider your table design. 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] Performance on large functions
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I can't help but feel that, because functions wrap everything in a > transaction, some sort of tinkering with the xlog settings/performance > is called for ... I kinda doubt it. More likely sources of trouble involve the optimizer picking bad plans because it can't see the exact values being used in queries. Can you show us the details of the function? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Performance on large functions
Tom, > I kinda doubt it. More likely sources of trouble involve the > optimizer > picking bad plans because it can't see the exact values being used in > queries. Can you show us the details of the function? Thanks for the response. Chris Ruprecht and I got into an off-list conversation and basically determined that the problem is 90% likely to be disk access time. Bummer 'cause I didn't want to add any hardware to this machine, but, well, there ya go. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(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] Performance on large functions
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Thanks for the response. Chris Ruprecht and I got into an off-list > conversation and basically determined that the problem is 90% likely to > be disk access time. Hmm. Can you cut the amount of disk access by reducing the number of updates that you do? Twelve passes of updates seems like a lot. Maybe you could restructure things to allow the data to be assembled in fewer steps. regards, tom lane ---(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] WHERE on an alias
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Basically there is the utable, which has two keys in it. I want to > select some rows from the utable. Some columns come from the dtable, > which has a key that matches to one of the utable keys, and I want to > select the last value from the mltable which has the same two keys as > utable and it has a serial value that I use do determine which is the > last entry. I also want to use the last value from the mltable in the > WHERE clause. I'm thinking you could use a subselect along the lines of select distinct on (ml.f1,ml.f2) * from ut,ml where ut.f1 = ml.f1 and ut.f2 = ml.f2 order by ml.f1, ml.f2, ml.f3 desc; f1 and f2 being the match keys and f3 being the serial value. Given two-key indexes, I get plans along the lines of Unique (cost=109.96..110.08 rows=2 width=20) -> Sort (cost=109.96..109.96 rows=25 width=20) -> Merge Join (cost=0.00..109.38 rows=25 width=20) -> Index Scan using ut_pkey on ut (cost=0.00..52.00 rows=1000 width=8) -> Index Scan using ml_f1f2 on ml (cost=0.00..52.00 rows=1000 width=12) which doesn't look too bad. 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] WHERE on an alias
I'm not clear how this helps. I want to get the last entry of ml. The distinct on means I won't get duplicate entries with the same key values, but what specifies that I'm getting the last ml value, or even one ml value at all? Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Basically there is the utable, which has two keys in it. I want to >>select some rows from the utable. Some columns come from the dtable, >>which has a key that matches to one of the utable keys, and I want to >>select the last value from the mltable which has the same two keys as >>utable and it has a serial value that I use do determine which is the >>last entry. I also want to use the last value from the mltable in the >>WHERE clause. >> > > I'm thinking you could use a subselect along the lines of > > select distinct on (ml.f1,ml.f2) * from ut,ml > where ut.f1 = ml.f1 and ut.f2 = ml.f2 > order by ml.f1, ml.f2, ml.f3 desc; > > f1 and f2 being the match keys and f3 being the serial value. > Given two-key indexes, I get plans along the lines of > > Unique (cost=109.96..110.08 rows=2 width=20) > -> Sort (cost=109.96..109.96 rows=25 width=20) > -> Merge Join (cost=0.00..109.38 rows=25 width=20) > -> Index Scan using ut_pkey on ut (cost=0.00..52.00 rows=1000 >width=8) > -> Index Scan using ml_f1f2 on ml (cost=0.00..52.00 rows=1000 >width=12) > > which doesn't look too bad. > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(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] WHERE on an alias
Joseph Shraibman <[EMAIL PROTECTED]> writes: > I'm not clear how this helps. I want to get the last entry of ml. > The distinct on means I won't get duplicate entries with the same key > values, but what specifies that I'm getting the last ml value, or even > one ml value at all? The DISTINCT ensures you get only one row per f1/f2 combination, and the use of ORDER BY together with DISTINCT ON forces it to be the row with the maximal ml value. See the SELECT reference page. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
