[COMMITTERS] pgsql: Fix btree_gist to handle timetz zone correctly per recent
Log Message: --- Fix btree_gist to handle timetz zone correctly per recent changes. Modified Files: -- pgsql/contrib/btree_gist: btree_time.c (r1.5 -> r1.6) (http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/btree_gist/btree_time.c.diff?r1=1.5&r2=1.6) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[COMMITTERS] pgsql: Re-add item with better description: > * Allow ORDER BY ...
Log Message: --- Re-add item with better description: > * Allow ORDER BY ... LIMIT 1 to select high/low value without sort or > index using a sequential scan for highest/lowest values > > Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort > all values to return the high/low value. Instead The idea is to do a > sequential scan to find the high/low value, thus avoiding the sort. > Modified Files: -- pgsql/doc: TODO (r1.1515 -> r1.1516) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1515&r2=1.1516) pgsql/doc/src/FAQ: TODO.html (r1.22 -> r1.23) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/TODO.html.diff?r1=1.22&r2=1.23) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [COMMITTERS] pgsql: Remove item, not sure what it refers to:
Kris Jurka wrote:
>
>
> On Sat, 23 Apr 2005, Bruce Momjian wrote:
>
> > Log Message:
> > ---
> > Remove item, not sure what it refers to:
> >
> > < * Allow ORDER BY ... LIMIT 1 to select high/low value without sort or
> > < index using a sequential scan for highest/lowest values
> > <
> > < If only one value is needed, there is no need to sort the entire
> > < table. Instead a sequential scan could get the matching value.
> > <
>
> This is actually a suggestion from Oleg here:
>
> http://archives.postgresql.org/pgsql-general/2002-04/msg00464.php
>
> double min = DBL_MAX;
> for (i=0; i if (data[i] < min) {
> min = data[i];
> }
> }
OK, so you are saying that right now if we want ORDER BY ... LIMIT 1,
and there is no index, we sort the result then pick the high value,
rather than just doing a sequential scan and grabbing the high/low
value. Makes sense now.
Thanks, TODO item readded with a clearer description:
* Allow ORDER BY ... LIMIT 1 to select high/low value without sort or
index using a sequential scan for highest/lowest values
Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort
all values to return the high/low value. Instead The idea is to do a
sequential scan to find the high/low value, thus avoiding the sort.
--
Bruce Momjian| http://candle.pha.pa.us
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [COMMITTERS] pgsql: Remove item, not sure what it refers to:
Stephen Frost wrote: -- Start of PGP signed section. > * Bruce Momjian ([email protected]) wrote: > > Thanks, TODO item readded with a clearer description: > > > > * Allow ORDER BY ... LIMIT 1 to select high/low value without sort or > > index using a sequential scan for highest/lowest values > > > > Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort > > all values to return the high/low value. Instead The idea is to do a > > sequential scan to find the high/low value, thus avoiding the sort. > > Could we take this perhaps a step further and consider things like > 'LIMIT 10' and come up with an approximate point where the trade-off > exists? Actually, thinking about this a minute more perhaps there isn't > even a trade-off to be made... What you're suggesting is basically a > size-of-1 temporary memory structure for the 'sort'. Isn't there > already a memory structure used to perform the sorting though? Could it > be adjusted such that it's of a fixed size when 'LIMIT' is given, as > above? > > Just some thoughts, while I think the specific 'LIMIT 1' case is > probably pretty common I think the 'LIMIT 10' or 'LIMIT 50' (or however > many you want to display on the webpage...) is a pretty common use case > too and it sounds like we could improve those too with this mechanism. Yes, I think the final optimization will allow >1 values for LIMIT. -- Bruce Momjian| http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[COMMITTERS] tablelog - tablelog: Created function to create view that will do a
Log Message: --- Created function to create view that will do a backwards restore Modified Files: -- tablelog: table_log_restore.sql (r1.1 -> r1.2) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/tablelog/tablelog/table_log_restore.sql.diff?r1=1.1&r2=1.2) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [COMMITTERS] pgsql: Remove item, not sure what it refers to:
* Bruce Momjian ([email protected]) wrote: > Thanks, TODO item readded with a clearer description: > > * Allow ORDER BY ... LIMIT 1 to select high/low value without sort or > index using a sequential scan for highest/lowest values > > Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort > all values to return the high/low value. Instead The idea is to do a > sequential scan to find the high/low value, thus avoiding the sort. Could we take this perhaps a step further and consider things like 'LIMIT 10' and come up with an approximate point where the trade-off exists? Actually, thinking about this a minute more perhaps there isn't even a trade-off to be made... What you're suggesting is basically a size-of-1 temporary memory structure for the 'sort'. Isn't there already a memory structure used to perform the sorting though? Could it be adjusted such that it's of a fixed size when 'LIMIT' is given, as above? Just some thoughts, while I think the specific 'LIMIT 1' case is probably pretty common I think the 'LIMIT 10' or 'LIMIT 50' (or however many you want to display on the webpage...) is a pretty common use case too and it sounds like we could improve those too with this mechanism. Thoughts? Thanks, Stephen signature.asc Description: Digital signature
Re: [COMMITTERS] pgsql: Remove item, not sure what it refers to:
On Mon, 25 Apr 2005, Stephen Frost wrote: * Bruce Momjian ([email protected]) wrote: Thanks, TODO item readded with a clearer description: * Allow ORDER BY ... LIMIT 1 to select high/low value without sort or index using a sequential scan for highest/lowest values Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort all values to return the high/low value. Instead The idea is to do a sequential scan to find the high/low value, thus avoiding the sort. Could we take this perhaps a step further and consider things like 'LIMIT 10' and come up with an approximate point where the trade-off exists? Actually, thinking about this a minute more perhaps there isn't even a trade-off to be made... What you're suggesting is basically a size-of-1 temporary memory structure for the 'sort'. Isn't there already a memory structure used to perform the sorting though? Could it be adjusted such that it's of a fixed size when 'LIMIT' is given, as above? Just some thoughts, while I think the specific 'LIMIT 1' case is probably pretty common I think the 'LIMIT 10' or 'LIMIT 50' (or however many you want to display on the webpage...) is a pretty common use case too and it sounds like we could improve those too with this mechanism. It's a question of when to stop sorting, so, yes, it should be doable. Thoughts? Thanks, Stephen Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [email protected], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [COMMITTERS] pgsql: Re-add item with better description: > * Allow ORDER BY ...
[EMAIL PROTECTED] (Bruce Momjian) writes: > Re-add item with better description: >> * Allow ORDER BY ... LIMIT 1 to select high/low value without sort or >> index using a sequential scan for highest/lowest values >> >> Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort >> all values to return the high/low value. Instead The idea is to do a >> sequential scan to find the high/low value, thus avoiding the sort. That's not much better, as it simply begs the question "why not use MIN/MAX"? I think the real point of Oleg's suggestion is a better way to do "top N" queries. This does not get interesting unless N > 1. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[COMMITTERS] pgsql: Update description: < * Allow ORDER BY ...
Log Message: --- Update description: < * Allow ORDER BY ... LIMIT 1 to select high/low value without sort or > * Allow ORDER BY ... LIMIT # to select high/low value without sort or 868c868 < Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort > Right now, if no index exists, ORDER BY ... LIMIT # requires we sort 870a871 > MIN/MAX already does this, but not for LIMIT > 1. Modified Files: -- pgsql/doc: TODO (r1.1516 -> r1.1517) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1516&r2=1.1517) pgsql/doc/src/FAQ: TODO.html (r1.23 -> r1.24) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/TODO.html.diff?r1=1.23&r2=1.24) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [COMMITTERS] pgsql: Re-add item with better description: > * Allow
Tom Lane wrote: > [EMAIL PROTECTED] (Bruce Momjian) writes: > > Re-add item with better description: > > >> * Allow ORDER BY ... LIMIT 1 to select high/low value without sort or > >> index using a sequential scan for highest/lowest values > >> > >> Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort > >> all values to return the high/low value. Instead The idea is to do a > >> sequential scan to find the high/low value, thus avoiding the sort. > > That's not much better, as it simply begs the question "why not use > MIN/MAX"? > I think the real point of Oleg's suggestion is a better way to do "top N" > queries. This does not get interesting unless N > 1. Good point, updated: * Allow ORDER BY ... LIMIT # to select high/low value without sort or index using a sequential scan for highest/lowest values Right now, if no index exists, ORDER BY ... LIMIT # requires we sort all values to return the high/low value. Instead The idea is to do a sequential scan to find the high/low value, thus avoiding the sort. MIN/MAX already does this, but not for LIMIT > 1. -- Bruce Momjian| http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[COMMITTERS] pgsql: Fix btree_gist to handle timetz zone correctly per recent
Log Message: --- Fix btree_gist to handle timetz zone correctly per recent changes. Tags: REL8_0_STABLE Modified Files: -- pgsql/contrib/btree_gist: btree_time.c (r1.3.4.4 -> r1.3.4.5) (http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/btree_gist/btree_time.c.diff?r1=1.3.4.4&r2=1.3.4.5) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[COMMITTERS] pgsql: get_expr_result_type probably needs to be able to handle OpExpr
Log Message: --- get_expr_result_type probably needs to be able to handle OpExpr as well as FuncExpr, to cover cases where a function returning tuple is invoked via an operator. Modified Files: -- pgsql/src/backend/utils/fmgr: funcapi.c (r1.20 -> r1.21) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/fmgr/funcapi.c.diff?r1=1.20&r2=1.21) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[COMMITTERS] pgsql: Fix ExpandIndirectionStar to handle cases where the expression to
Log Message: --- Fix ExpandIndirectionStar to handle cases where the expression to be expanded is of RECORD type, eg 'select (foo).* from (select foo(f1) from t1) ss' where foo() is a function declared with multiple OUT parameters. Modified Files: -- pgsql/src/backend/parser: parse_target.c (r1.131 -> r1.132) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/parse_target.c.diff?r1=1.131&r2=1.132) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[COMMITTERS] oledb - oledb: Added experimental support for "bytea".
Log Message: --- Added experimental support for "bytea". Modified Files: -- oledb: PgRS.cpp (r1.38 -> r1.39) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/oledb/oledb/PgRS.cpp.diff?r1=1.38&r2=1.39) PgRS.h (r1.24 -> r1.25) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/oledb/oledb/PgRS.h.diff?r1=1.24&r2=1.25) PgSess.cpp (r1.36 -> r1.37) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/oledb/oledb/PgSess.cpp.diff?r1=1.36&r2=1.37) PgSess.h (r1.35 -> r1.36) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/oledb/oledb/PgSess.h.diff?r1=1.35&r2=1.36) TypeInfo.cpp (r1.16 -> r1.17) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/oledb/oledb/TypeInfo.cpp.diff?r1=1.16&r2=1.17) TypeInfo.h (r1.16 -> r1.17) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/oledb/oledb/TypeInfo.h.diff?r1=1.16&r2=1.17) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[COMMITTERS] pgsql: On further experimentation, there were still a couple of bugs in
Log Message: --- On further experimentation, there were still a couple of bugs in ExpandIndirectionStar() ... and in markTargetListOrigin() too. Modified Files: -- pgsql/src/backend/parser: parse_target.c (r1.132 -> r1.133) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/parse_target.c.diff?r1=1.132&r2=1.133) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[COMMITTERS] pgsql: Mention that PAM requires the user already exist in the database,
Log Message: --- Mention that PAM requires the user already exist in the database, per Dick Davies. Modified Files: -- pgsql/doc/src/sgml: client-auth.sgml (r1.76 -> r1.77) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/client-auth.sgml.diff?r1=1.76&r2=1.77) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[COMMITTERS] pgsql: Mention that PAM requires the user already exist in the database,
Log Message: --- Mention that PAM requires the user already exist in the database, per Dick Davies. Tags: REL8_0_STABLE Modified Files: -- pgsql/doc/src/sgml: client-auth.sgml (r1.70.4.4 -> r1.70.4.5) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/client-auth.sgml.diff?r1=1.70.4.4&r2=1.70.4.5) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [COMMITTERS] pgsql: Mention that PAM requires the user already exist in the database,
[EMAIL PROTECTED] (Bruce Momjian) writes: > Mention that PAM requires the user already exist in the database, per > Dick Davies. I don't recall exactly what Dick suggested, but the patch as applied seems like fairly useless verbiage. Exactly which of our other auth methods allow users who *don't* exist in the database to log in? And why would anyone find it surprising that this does not happen? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
