Re: [HACKERS] Fixup some misusage of appendStringInfo and friends

2017-08-15 Thread David Rowley
On 16 August 2017 at 15:38, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > committed Thanks! -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (

Re: [HACKERS] Foreign Join pushdowns not working properly for outer joins

2017-04-26 Thread David Rowley
> > For backpatching to 9.6, I came up with the attached reduced version. > Since we don't have add_foreign_grouping_paths() in 9.6, we can omit the > refactoring and keep the changes much simpler. Does that make sense? That makes sense to me. It fixes the reported issue and is less invasive t

Re: [HACKERS] Foreign Join pushdowns not working properly for outer joins

2017-04-26 Thread David Rowley
On 27 April 2017 at 01:31, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > committed Great. Thanks! -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (

Re: [HACKERS] Fixup some misusage of appendStringInfo and friends

2017-04-26 Thread David Rowley
our better judgment and add to the next 'fest. Thanks -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: htt

[HACKERS] Incorrect use of ERRCODE_UNDEFINED_COLUMN in extended stats

2017-04-24 Thread David Rowley
The attached small patched fixes an incorrect usage of an error code in the extended stats code. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services ext_stats_duplicate_column_errorcode_fix.patch Description: Binary

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

2017-04-28 Thread David Rowley
s done with the other join types seems to fix the issue. I don't know yet if that's the correct fix. It's pretty late 'round this side to be thinking too hard about it. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Servic

Re: [HACKERS] PG 10 release notes

2017-04-24 Thread David Rowley
ee into account when multiplying selectivity estimations for multiple columns. Unsure how best to trim that down to something short enough for the release notes. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sen

Re: [HACKERS] Incorrect use of ERRCODE_UNDEFINED_COLUMN in extended stats

2017-04-25 Thread David Rowley
On 25 April 2017 at 02:15, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> The attached small patched fixes an incorrect usage of an error code >> in the extended stats code. > > Hmm, looks like all of that cou

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

2017-04-28 Thread David Rowley
On 29 April 2017 at 00:45, Alexander Korotkov <a.korot...@postgrespro.ru> wrote: > On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query > result is OK. Hi, Did you mean to attach this? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL

Re: [HACKERS] Atomics for heap_parallelscan_nextpage()

2017-08-16 Thread David Rowley
for handling the cleanup too. I'll feel better once pademelon goes green again. From looking at the latest failure on it, it appears that your swapping of pg_atomic_write_u64 for pg_atomic_init_u64 should fix this. My apologies for that mistake. -- David Rowley http://www.2nd

Re: [HACKERS] [GENERAL] Remove useless joins (VARCHAR vs TEXT)

2017-09-16 Thread David Rowley
is being a bug. The attached fixes. (CC'd -hackers since we're starting to discuss code changes. Further discussion which includes -hackers should drop the general list) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Servic

Re: [HACKERS] path toward faster partition pruning

2017-09-14 Thread David Rowley
gt; partitions one-by-one. It's not fully cooked yet though. I'm interested in seeing improvements in this area, so I've put my name down to review this. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via

Re: [HACKERS] JIT compiling - v4.0

2017-10-05 Thread David Rowley
s some JIT threshold GUC. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-10 Thread David Rowley
mn? 2.400209476818 (1 row) Maybe it's worth trying with different row counts to see if the additional cost is consistent, but it's probably not worth being too critical here. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Trainin

Re: [HACKERS] Extended statistics is not working on Vars hidden under a RelabelType

2017-10-16 Thread David Rowley
On 15 October 2017 at 06:49, Robert Haas <robertmh...@gmail.com> wrote: > On Fri, Oct 13, 2017 at 4:49 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> tps = 8282.481310 (including connections establishing) >> tps = 8282.750821 (excluding connections

Re: [HACKERS] Discussion on missing optimizations

2017-10-13 Thread David Rowley
Perhaps I missed some? It looks like there's plenty we could do in there, just nobody seems interested enough to go and do it, everyone who cares about performance is too busy trying to make execution run faster. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development,

Re: [HACKERS] Extended statistics is not working on Vars hidden under a RelabelType

2017-10-13 Thread David Rowley
On 14 October 2017 at 09:04, Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Oct 9, 2017 at 11:03 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> -- Unpatched >> Planning time: 0.184 ms >> Execution time: 105.878 ms >> >> -- Patche

Re: [HACKERS] Extended statistics is not working on Vars hidden under a RelabelType

2017-10-12 Thread David Rowley
ame thing. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Discussion on missing optimizations

2017-10-12 Thread David Rowley
ecent percentage of SELECTs too. I had imagined this would be some backend local cache that saved MRU plans up to some size of memory defined by a GUC, where 0 would disable the feature. I never got any further than those thoughts. -- David Rowley http://www.2ndQuadrant.c

Re: [HACKERS] Extended statistics is not working on Vars hidden under a RelabelType

2017-10-12 Thread David Rowley
On 13 October 2017 at 04:56, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > I pushed your original fix. Thanks for committing -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers

Re: [HACKERS] Aggregate transition state merging vs. hypothetical set functions

2017-10-12 Thread David Rowley
ggregates or for all? If the user defines their normal aggregate as not safe for merging, then surely it'll not be suitable to be used as a window function either, since the final function will also be called there multiple times per state. -- David Rowley http://www.2ndQuadrant.

Re: [HACKERS] Aggregate transition state merging vs. hypothetical set functions

2017-10-12 Thread David Rowley
On 13 October 2017 at 12:41, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> If the user defines their normal aggregate as not safe for merging, >> then surely it'll not be suitable to be used as a window function >>

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-13 Thread David Rowley
't really think the DEFAULT_APPEND_COST_FACTOR adds much. it means very little by itself. It also seems that most of the other cost functions just use the magic number. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Serv

Re: [HACKERS] Making clausesel.c Smarter

2017-09-05 Thread David Rowley
bably would see some resistance, so won't have time to look at this soon. If the possibility of this increasing planning time in corner cases is going to be a problem, then it might be best to return this with feedback for now and I'll resubmit if I get time later in the cycle. --

Re: [HACKERS] path toward faster partition pruning

2017-09-26 Thread David Rowley
tgresql.org/gitweb/?p=postgresql.git;a=commit;h=9140cf826 Yeah, I see 0001 conflicts with that. I'm going to set this to waiting on author while you're busy rebasing this. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent

Re: [HACKERS] path toward faster partition pruning

2017-09-28 Thread David Rowley
and that would likely tidy it up a bit. The matching partition indexes could be returned as a Bitmapset, yet, I don't really see any code which handles adding the NULL and DEFAULT partition in get_rel_partitions() either, maybe I've just not looked hard enough yet... -- David Rowley

Re: [HACKERS] CONNECTION LIMIT and Parallel Query don't play well together

2017-08-25 Thread David Rowley
ier in this thread about this and I don't think anyone was in favour of queries randomly working sometimes. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-09 Thread David Rowley
e's even a comment in create_append_path() which claims the zero cost is a bit optimistic. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) T

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread David Rowley
ommon (at least in my world). Thoughts? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services remove_left_join_distinct.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgres

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread David Rowley
On 9 October 2017 at 17:41, David Rowley <david.row...@2ndquadrant.com> wrote: > Thoughts? Actually, I was a little inconsistent with my List NULL/NIL checks in that last one. I've attached an updated patch. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL De

[HACKERS] Extended statistics is not working on Vars hidden under a RelabelType

2017-10-09 Thread David Rowley
8 rows) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services allow_relabelled_vars_in_dependency_stats.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your su

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread David Rowley
pin and finding performance is not quite what they need. It's a bit sad that often the people with the loudest voices are always so fast to stomp on the ideas for improvements. It would be much nicer if you'd at least wait for benchmarks before shooting. -- David Rowley http://www.2

[HACKERS] A handful of typos in allpaths.c

2017-10-17 Thread David Rowley
A small patch to fix these is attached. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services allpath_typos_fix.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To

[HACKERS] No mention of CREATE STATISTICS in event trigger docs

2017-11-10 Thread David Rowley
A patch to fix this is attached. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services event_trigger_statistics_doc.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To

Re: [HACKERS] UPDATE of partition key

2017-11-13 Thread David Rowley
TO p VALUES('t', 10); SELECT tableoid::regclass,oid,a FROM p; tableoid | oid | a --+---+--- p_true | 16792 | t (1 row) UPDATE p SET a = 'f'; -- partition-key-UPDATE (oid has changed (it probably shouldn't have)) SELECT tableoid::regclass,oid,a FROM p; tableoid | oid | a --

Re: [HACKERS] path toward faster partition pruning

2017-11-05 Thread David Rowley
On 3 November 2017 at 17:32, David Rowley <david.row...@2ndquadrant.com> wrote: > 2. This code is way more complex than it needs to be. > > if (num_parts > 0) > { > int j; > > all_indexes = (int *) palloc(num_parts * sizeof(int)); > j = 0; > if (min_part_idx >

Re: [HACKERS] path toward faster partition pruning

2017-11-05 Thread David Rowley
d to be allowed too. This works slightly differently. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http:/

Re: [Sender Address Forgery]Re: [HACKERS] path toward faster partition pruning

2017-11-05 Thread David Rowley
On 6 November 2017 at 17:30, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > On 2017/11/03 13:32, David Rowley wrote: >> On 31 October 2017 at 21:43, Amit Langote <langote_amit...@lab.ntt.co.jp> >> wrote: >> 1. This comment seem wrong. >&

[HACKERS] Removing useless DISTINCT clauses

2017-11-06 Thread David Rowley
.git;a=commit;h=d4c3a156cb46dcd1f9f97a8011bd94c544079bb5 [2] https://www.postgresql.org/message-id/flat/CAKJS1f9q0j3BgMUsDbtf9%3DecfVLnqvkYB44MXj0gpVuamcN8Xw%40mail.gmail.com#CAKJS1f9q0j3BgMUsDbtf9=ecfvlnqvkyb44mxj0gpvuamcn...@mail.gmail.com -- David Rowley http://www.2ndQuadrant.c

Re: [HACKERS] path toward faster partition pruning

2017-11-02 Thread David Rowley
latest patches conflict with cf7ab13bf. Can you send patches rebased on current master? Thanks -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] path toward faster partition pruning

2017-11-06 Thread David Rowley
On 7 November 2017 at 01:52, David Rowley <david.row...@2ndquadrant.com> wrote: > Thanks. I'll look over it all again starting my Tuesday morning. (UTC+13) I have a little more review to share: 1. Missing "in" in comment. Should be "mentioned in" * get_append_rel_

Re: [HACKERS] path toward faster partition pruning

2017-11-07 Thread David Rowley
On 7 November 2017 at 01:52, David Rowley <david.row...@2ndquadrant.com> wrote: > Thanks. I'll look over it all again starting my Tuesday morning. (UTC+13) Hi Amit, I had another look over this today. Apologies if any of the review seems petty. Here goes: 1. If test seems to b

Re: [HACKERS] path toward faster partition pruning

2017-11-09 Thread David Rowley
(~(bitmapword) 0) >> (BITS_PER_BITMAPWORD - (BITNUM(upper) - 1)); > = No objections here for making bms_add_range() perform better, but this is not going to work when lwordnum == uwordnum. You'd need to special case that. I didn't think it was worth the trouble, but maybe it is... I

[HACKERS] ArrayLists instead of List (for some things)

2017-11-02 Thread David Rowley
elcome, but I was too late to the commitfest, so there are other priorities. However, if you have a strong opinion, feel free to voice it. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services 0001-Basic-implementation-of-array-lists-AList.

Re: [HACKERS] ArrayLists instead of List (for some things)

2017-11-02 Thread David Rowley
(). Probably many of those places could be changed to lappend(), but I bet there's plenty that need prepend. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgre

Re: [HACKERS] ArrayLists instead of List (for some things)

2017-11-02 Thread David Rowley
On 3 November 2017 at 03:38, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> On 3 November 2017 at 03:17, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> We've jacked up the List API and driven a new implementation undernea

Re: [HACKERS] ArrayLists instead of List (for some things)

2017-11-02 Thread David Rowley
On 3 November 2017 at 03:27, Stephen Frost <sfr...@snowman.net> wrote: > * David Rowley (david.row...@2ndquadrant.com) wrote: >> We could get around a few of these problems if Lists were implemented >> internally as arrays, however, arrays are pretty bad if we want to

Re: [HACKERS] path toward faster partition pruning

2017-11-02 Thread David Rowley
s, -1, PARTITION_MAX_KEYS * sizeof(NullTestType *)); should be PARTITION_MAX_KEYS * sizeof(NullTestType). It might have worked on your machine if you're compiling as 32 bit. I'll continue on with the review in the next few days. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL

Re: [HACKERS] ArrayLists instead of List (for some things)

2017-11-02 Thread David Rowley
On 3 November 2017 at 03:26, Craig Ringer <cr...@2ndquadrant.com> wrote: > On 2 November 2017 at 22:22, David Rowley <david.row...@2ndquadrant.com> > wrote: >> Maybe, but the new implementation is not going to do well with places >> where we perform lcons(). Proba

Re: [HACKERS] path toward faster partition pruning

2017-11-06 Thread David Rowley
, including the fix to make the new pruning > code handle Boolean partitioning. Thanks. I'll look over it all again starting my Tuesday morning. (UTC+13) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- S

Re: [HACKERS] An unlikely() experiment

2017-10-30 Thread David Rowley
On 30 October 2017 at 22:34, Andres Freund <and...@anarazel.de> wrote: > Hi, > > On 2015-12-20 02:49:13 +1300, David Rowley wrote: >> Alternatively, if there was some way to mark the path as cold from within >> the path, rather than from the if() condition before the pat

Re: [HACKERS] An unlikely() experiment

2017-10-30 Thread David Rowley
On 30 October 2017 at 22:44, Andres Freund <and...@anarazel.de> wrote: > On 2017-10-30 22:39:01 +1300, David Rowley wrote: >> Today I was thinking, to get around that issue, we might be able to >> generate another thin wrapper around elog_start() and mark that as >> __at

[HACKERS] Removing [Merge]Append nodes which contain a single subpath

2017-10-25 Thread David Rowley
stgresql.org/message-id/CAKJS1f9UXdk6ZYyqbJnjFO9a9hyHKGW7B%3DZRh-rxy9qxfPA5Gw%40mail.gmail.com -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

[HACKERS] Removing LEFT JOINs in more cases

2017-10-31 Thread David Rowley
. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services 0001-Support-removing-LEFT-JOINs-with-DISTINCT-GROUP-BY.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make cha

Re: [HACKERS] Removing [Merge]Append nodes which contain a single subpath

2017-10-26 Thread David Rowley
On 26 October 2017 at 23:42, Antonin Houska <a...@cybertec.at> wrote: > David Rowley <david.row...@2ndquadrant.com> wrote: > >> Method 1: >> >> In set_append_rel_size() detect when just a single subpath would be >> added to the Append path. > > I s

Re: [HACKERS] Removing [Merge]Append nodes which contain a single subpath

2017-10-26 Thread David Rowley
On 26 October 2017 at 23:30, Robert Haas <robertmh...@gmail.com> wrote: > On Wed, Oct 25, 2017 at 11:59 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> As of today, because we include this needless [Merge]Append node, we >> cannot parallelise scans

Re: [HACKERS] Removing [Merge]Append nodes which contain a single subpath

2017-10-26 Thread David Rowley
--+- a | integer | | | | plain | | b | integer | | | | plain | | Partition key: RANGE (a) Partitions: ab_p1 FOR VALUES FROM (1) TO (1), ab_p2 FOR VALUES FROM (10000) TO (2) -- D

[HACKERS] TODO item: Implement Boyer-Moore searching (First time hacker)

2008-08-30 Thread David Rowley
My apologies if this is a duplicate. I think the attachment size was too big. Reference: Bruce Momjian writes: - http://archives.postgresql.org/pgsql-committers/2007-09/msg00402.php Other references: Boyer Moore?? -

Re: [HACKERS] TODO item: Implement Boyer-Moore searching (First time hacker)

2008-08-31 Thread David Rowley
if there is anything that I've overlooked. David. -Original Message- From: David Rowley [mailto:[EMAIL PROTECTED] Sent: 31 August 2008 00:21 To: 'pgsql-hackers@postgresql.org' Subject: TODO item: Implement Boyer-Moore searching (First time hacker) Reference: Bruce Momjian writes

Re: [PATCHES] [HACKERS] TODO item: Implement Boyer-Moore searching (First time hacker)

2008-09-06 Thread David Rowley
Moved from pgsql-patches Tom Lane wrote: I wrote: I looked this over a bit and was immediately confused by one thing: the introductory comment says that the skip table size ought to be based on the length of the haystack, which makes sense to me, but the code is actually initializing it

Re: [PATCHES] [HACKERS] TODO item: Implement Boyer-Moore searching (First time hacker)

2008-09-07 Thread David Rowley
Tom Lane Wrote: David Rowley [EMAIL PROTECTED] writes: I've made the discussed changes. Also updated the benchmark results. http://www.unixbeast.com/~fat/8.3_test_v1.3.xls Applied with revisions; mostly cosmetic except for one point. I realized after studying the code a bit more that B-M

[HACKERS] TODO item: Allow substring/replace() to get/set bit values

2008-09-07 Thread David Rowley
I was just searching though the TODO list to try and find a quick one that could be ticked off. Under functions there is Allow substring/replace() to get/set bit values. Does anyone have any more information about this?

Re: [PATCHES] [HACKERS] TODO item: Implement Boyer-Moore searching (First time hacker)

2008-09-08 Thread David Rowley
Heikki Linnakangas wrote: David Rowley wrote: Thanks for all the reviews and suggestions. David, could you re-run the performance tests you ran earlier? I'm curious to know what impact switching to the simpler loop for 1-byte pattern had. Sure: http://www.unixbeast.com/~fat/8.3_test_v1.4

Re: [HACKERS] Window Functions patch v06

2008-10-13 Thread David Rowley
Hitoshi Harada wrote: I made up my mind to scratch former window functions and redesigned completely new execution model, based on the discussion with Heikki. Attached is the v06 against HEAD today. http://umitanuki.net/pgsql/wfv06/design.html First off, fantastic work! In my eyes this and WITH

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-27 Thread David Rowley
Hitoshi Harada Wrote: As I promised, version 7 of the window functions is now released. At the same time, git repository branch comes back to master. git: http://git.postgresql.org/?p=~davidfetter/window_functions/.git patch: http://umitanuki.net/pgsql/window_functions.patch.20081028.gz

Re: [HACKERS] TABLE command

2008-10-30 Thread David Rowley
Peter Eisentraut Wrote: If I read this right, SQL would allow writing TABLE foo; Interesting; I managed to find it in the spec: Quote 4) The explicit table TABLE table or query name is equivalent to the table subquery ( SELECT * FROM table or query name ) /Quote So going by that would the

[HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-01 Thread David Rowley
All, This is my first patch review for PostgreSQL. I did submit a patch last commit fest (Boyer-Moore) so I feel I should review one this commit fest. I'm quite new to PostgreSQL so please don't rely on me totally. I'll do my best. Heikki is also reviewing this patch which makes me feel better.

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-02 Thread David Rowley
that people will probably use this method as sometimes there is little choice to get the performance that is required. Hitoshi Harada Wrote: 2008/11/2 David Rowley [EMAIL PROTECTED]: Obervations: Test 3 and 5 did not seem to make use of an index to get a sorted list of results. I disabled

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-04 Thread David Rowley
Hitoshi Harada wrote: Test 3 and 5 did not seem to make use of an index to get a sorted list of results. I disabled enable_seqscan but the planner still failed to choose index_scan. Is there any reason for this? Perhaps I'm missing something. Hitoshi, can you take a look at this?

[HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-04 Thread David Rowley
I wrote: All, This is my first patch review for PostgreSQL. I did submit a patch last commit fest (Boyer-Moore) so I feel I should review one this commit fest. I'm quite new to PostgreSQL so please don't rely on me totally. I'll do my best. Heikki is also reviewing this patch which makes me

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-08 Thread David Rowley
Hitoshi Harada Wrote: although attached is the whole (split) patch. I'm having some trouble getting these patches to patch cleanly. I think it's because of this that I can't get postgres to compile after applying the patch. It errors out at tuptoaster.c some constants seem to be missing from

[HACKERS] Windowing Function Patch Review - NTILE function

2008-11-09 Thread David Rowley
I've done a little testing with NTILE(). I think a check should be added to the ntile() function in wfunc.c. david=# select name,salary,ntile(0) over (order by salary) as n from employees; ERROR: floating-point exception DETAIL: An invalid floating-point operation was signaled. This probably

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-09 Thread David Rowley
Using one of my original test tables I'm testing windowing functions with a GROUP BY. The following query works as I would expect. -- Works SELECT department, SUM(Salary), ROW_NUMBER() OVER (ORDER BY department), SUM(SUM(salary)) OVER (ORDER BY department) FROM employees

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-09 Thread David Rowley
Hitoshi Harada wrote: I'm glad to hear that. Actually thanks to git it is quite easy for me to merge my own repository with the HEAD. It tells me which lines are new coming and which lines I modified are newer than else in CVS. This is my first project where I use git (and I am not guru of cvs

[HACKERS] Windowing Function Patch Review - NTH_VALUE

2008-11-09 Thread David Rowley
I'm having a little trouble understanding the standard for NTH_VALUE(). I would have assumed that NTH_VALUE(name,1) would return the first name in the window. The current patch is using 0 for the first. Here is the paragraph I'm reading in the standard: The nth-value function takes an arbitrary

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-09 Thread David Rowley
Hitoshi Harada wrote: I recreate the patch against current HEAD, in the git it's here: http://git.postgresql.org/?p=postgresql.git;a=commit;h=f88970d3c6fb9f99543 d873bb7228f4c057c23e0 I tested `patch -p1` with the attached and succeeded to make it work cleanly. It seems to me that this

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-09 Thread David Rowley
Hitoshi Harada wrote: I found how to do it, though it's only on the case you gave. Thinking about the planner optimization of the Window nodes (and its attached Sort nodes), we must consider the execution order of more than one node. In the test case we only take care of only one window, but

[HACKERS] Windowing Function Patch Review - ROW_NUMBER without ORDER BY

2008-11-09 Thread David Rowley
I've been trying to think of a use case for using ROW_NUMBER() with no ORDER BY in the window clause. Using the example table I always seem to be using, for those who missed it in other threads. create table employees ( id INT primary key, name varchar(30) not null, department varchar(30)

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-09 Thread David Rowley
Hitoshi Harada wrote: I found how to do it, though it's only on the case you gave. Thinking about the planner optimization of the Window nodes (and its attached Sort nodes), we must consider the execution order of more than one node. In the test case we only take care of only one window, but

Re: [HACKERS] Windowing Function Patch Review - ROW_NUMBER without ORDER BY

2008-11-10 Thread David Rowley
Hitoshi Harada wrote: The spec says: The ROW_NUMBER function computes the sequential row number, starting with 1 (one) for the first row, of the row within its window partition according to the window ordering of the window. I'm just not sure if we should block this or not. Does

Re: [HACKERS] SQL5 budget

2008-11-10 Thread David Rowley
Josh Berkus Wrote: I would suggest submitting it to pgsql-jobs. Except that it's not a job offer - it's more of a solicitation for implementation of something, so I'm not sure that's the right place either. No, but that's where the right people would see it. Realistically, this

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-14 Thread David Rowley
Hitoshi Harada wrote: david=# explain select date,lag(date,1) over (order by date) from meter_Readings order by date; QUERY PLAN

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-19 Thread David Rowley
I wrote: All, This is my first patch review for PostgreSQL. I did submit a patch last commit fest (Boyer-Moore) so I feel I should review one this commit fest. I'm quite new to PostgreSQL so please don't rely on me totally. I'll do my best. Heikki is also reviewing this patch which makes me

Re: [HACKERS] Cool hack with recursive queries

2008-11-19 Thread David Rowley
Gregory Stark wrote: So based on Graeme Job's T-SQL hack over at thedailywtf.com I adapted the T-SQL code to Postgres and got this. Thought others might find it amusing. WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS ( SELECT IX, IY, X::float, Y::float, X::float, Y::float, 0

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-25 Thread David Rowley
Hitoshi Harada wrote: 2008/11/20 David Rowley [EMAIL PROTECTED]: -- The following query gives incorrect results on the -- maxhighbid column SELECT auctionid, category, description, highestbid, reserve, MAX(highestbid) OVER (ORDER BY auctionid

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-26 Thread David Rowley
On 26/11/2008, Hitoshi Harada [EMAIL PROTECTED] wrote: 2008/11/26 David Rowley [EMAIL PROTECTED]: Hitoshi Harada wrote: 2008/11/20 David Rowley [EMAIL PROTECTED]: -- The following query gives incorrect results on the -- maxhighbid column SELECT auctionid, category

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-27 Thread David Rowley
I wrote: Hmm, did you apply the latest patch correctly? My build can produce right results, so I don't see why it isn't fixed. Make sure the lines around 2420-2430 in planner.c like: /* * must copyObject() to avoid args concatenating with

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-30 Thread David Rowley
-Original Message- From: Heikki Linnakangas [mailto:[EMAIL PROTECTED] Sent: 26 November 2008 09:09 To: Hitoshi Harada Cc: David Rowley; pgsql-hackers@postgresql.org Subject: Re: Windowing Function Patch Review - Standard Conformance Hitoshi Harada wrote: 2008/11/26 David Rowley

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-30 Thread David Rowley
I wrote: I was also reading over the standard tonight. I've discovered that the OFFSET in LEAD() and LAG() is optional. It should default to 1 if it is not present. Oracle seems to support this. SQL2008 says: If lead or lag function is specified, then: i) Let VE1 be lead or lag extent

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-05 Thread David Rowley
Hitoshi Harada wrote: I tested the spool performance with David's earlier bigtable: CREATE TABLE bigtable ( id SERIAL NOT NULL PRIMARY KEY, timestamp TIMESTAMP NOT NULL ); -- about 383MB of data INSERT INTO bigtable (timestamp) SELECT NOW() + (CAST(RANDOM() * 10 AS INT) || '

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-05 Thread David Rowley
Hitoshi Harada Wrote: 2008/12/3 Hitoshi Harada [EMAIL PROTECTED]: I am randomly trying some issues instead of agg common code (which I now doubt if it's worth sharing the code), so tell me if you're restarting your hack again. I'll send the whole patch. Attached is the updated patch,

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-07 Thread David Rowley
2008/12/7 Hitoshi Harada [EMAIL PROTECTED]: 2008/12/7 Hitoshi Harada [EMAIL PROTECTED]: 2008/12/6 David Rowley [EMAIL PROTECTED]: the time where the community can test further by committing this patch. Agree. I'll send the latest patch and finish my work for now. Here's the patch, including

Re: [HACKERS] Quick patch: Display sequence owner

2008-12-08 Thread David Rowley
Josh Williams wrote: Hi folks, Was recently poked and reminded that this patch may be of interest to the community. It was mostly done as an academic exercise, just to see how it works, and so it has a rather hackish feel. The patch adds the sequence owner, if available, to psql's \d

Re: [HACKERS] patch - per-tablespace random_page_cost/seq_page_cost

2009-11-28 Thread David Rowley
Robert Haas Wrote: Hmm. I'm not able to reliably detect a performance difference between unpatched CVS HEAD (er... git master branch) and same with spcoptions- v2.patch applied. I figured that if there were going to be an impact, it would be most likely to manifest itself in a query that

Re: [HACKERS] Frames vs partitions: is SQL2008 completely insane?

2008-12-27 Thread David Rowley
Hitoshi Harada wrote: I tested on Oracle 10.2.0, and the results are: select depname, empno, salary, lead(salary, 1) over (order by salary), lag(salary, 1) over (order by salary), first_value(salary) over (order by salary), last_value(salary) over (order by salary) from empsalary;

Re: [HACKERS] Frames vs partitions: is SQL2008 completely insane?

2008-12-27 Thread David Rowley
Hitoshi Harada wrote: I tested on Oracle 10.2.0, and the results are: select depname, empno, salary, lead(salary, 1) over (order by salary), lag(salary, 1) over (order by salary), first_value(salary) over (order by salary), last_value(salary) over (order by salary) from empsalary;

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-28 Thread David Rowley
Tom Lane Wrote: I've spent quite a bit of time reviewing the window functions patch, and I think it is now ready to commit, other than the documentation (which I've not looked at yet at all). Attached is my current patch against HEAD, sans documentation. This incorporates the recently

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-28 Thread David Rowley
Hitoshi Harada wrote: WITH RECURSIVE bom AS ( SELECT parentpart,childpart,quantity,ROW_NUMBER() OVER (ORDER BY parentpart DESC) rn FROM billofmaterials WHERE parentpart = 'KITCHEN' UNION ALL SELECT b.parentpart,b.childpart,b.quantity,ROW_NUMBER() OVER (ORDER BY parentpart ASC)

Re: [HACKERS] TODO items for window functions

2008-12-28 Thread David Rowley
Tom Lane Wrote: The core window-functions patch is now committed and ready for wider testing. However, there are a number of unfinished items, at least some of which I'd like to see addressed before 8.4 release. In rough order of importance: * Support creation of user-defined window

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-29 Thread David Rowley
Tom Lane Wrote: Well, this certainly demonstrates that the check I added to parseCheckAggregates is wrongly placed, but I'm not sure we really need to forbid the case. David's example query seems to give sane answers once the bug in begin_partition is fixed: parentpart | childpart |

  1   2   3   4   5   6   7   8   9   10   >