From:
Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To:
Robert Haas <robertmhaas(at)gmail(dot)com>
Cc:
Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, David Fetter <david(at)fetter(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject:
Re: wip: functions median and percentile
Date:
2010-10-04 19:20:28
Message-ID:
AANLkTi=T=8zAQzt88uBb0x7QXkb83OceGMvrh+pL796f@mail.gmail.com (view raw or flat )
Thread:
2010-08-19 10:59:33 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-08-19 14:50:23 from Greg Stark <gsstark(at)mit(dot)edu>
2010-08-19 15:33:13 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-08-19 16:49:45 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-08-19 17:03:20 from David Fetter <david(at)fetter(dot)org>
2010-08-19 17:12:12 from "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
2010-08-19 17:14:58 from David Fetter <david(at)fetter(dot)org>
2010-08-19 17:25:36 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-08-19 17:47:59 from David Fetter <david(at)fetter(dot)org>
2010-08-19 18:17:42 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-08-19 17:41:57 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-08-19 18:17:28 from "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
2010-08-19 17:38:14 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-20 03:47:09 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-09-20 05:11:02 from David Fetter <david(at)fetter(dot)org>
2010-09-20 06:22:29 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-21 20:28:15 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-21 20:44:16 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-09-21 21:03:31 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-22 02:21:01 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-09-22 04:36:24 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-22 03:06:51 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-09-22 04:44:58 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-23 09:35:47 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-23 17:45:36 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-09-23 18:27:38 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-23 18:48:47 from David Fetter <david(at)fetter(dot)org>
2010-09-23 18:56:53 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-26 10:39:21 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-01 08:17:19 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-01 08:46:45 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-01 13:05:43 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-01 13:19:57 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-01 14:11:08 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-01 14:43:47 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-01 15:00:35 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-01 15:08:00 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-01 15:15:03 from "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
2010-10-01 15:35:03 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-01 15:16:03 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-01 15:22:46 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-01 15:32:03 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-01 17:37:03 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-01 19:17:08 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-03 14:06:25 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-03 15:49:37 from Greg Stark <gsstark(at)mit(dot)edu>
2010-10-04 06:36:48 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-04 06:58:44 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-04 16:14:06 from Greg Stark <gsstark(at)mit(dot)edu>
2010-10-04 17:22:39 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-10-04 19:20:28 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-05 06:04:31 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-05 08:47:52 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-05 12:14:24 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-05 13:08:59 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-10 21:16:59 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-11 06:58:53 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-11 09:35:13 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-11 09:55:16 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-11 10:33:11 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-11 14:03:40 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-11 15:30:46 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-11 15:44:08 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-11 17:34:51 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-11 17:37:11 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-11 17:42:10 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-11 17:48:12 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-11 17:55:32 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-11 18:05:11 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-11 18:23:25 from Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
2010-10-11 10:33:12 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-10-11 14:08:53 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-10-11 14:29:38 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-10-11 18:27:13 from Greg Stark <gsstark(at)mit(dot)edu>
2010-10-11 18:30:39 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-10-11 18:46:47 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-12 03:08:32 from Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
2010-10-12 08:44:20 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-13 10:36:59 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-13 10:48:17 from Peter Eisentraut <peter_e(at)gmx(dot)net>
2010-10-13 10:51:51 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-13 10:56:06 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-14 00:54:42 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-10-14 02:37:37 from Greg Stark <gsstark(at)mit(dot)edu>
2010-10-14 12:17:54 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-10-14 04:53:57 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-03 17:31:18 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-10-01 14:44:44 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-09-23 13:22:39 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-09-23 13:25:32 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Lists:
pgsql-hackers pgsql-rrreviewers
On 4 October 2010 18:22, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Oct 4, 2010 at 2:58 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>> That requires a new sort for each row. I generated this with a minor
>> tweak to Pavel's patch to just restart the tuplesort each time (the
>> "quick-fix" solution). The problem is that performance really sucks,
>> because it is an O(n^2 log(n)) algorithm.
>
> Maybe that's OK. If you're doing repeated median operations on large
> data sets, perhaps you should expect that to be slow. I bet that
> people who want to use this as a window function will want one median
> per group, not n medians per group; and it doesn't seem like a good
> idea to say - we're not going to let you use this as a window function
> AT ALL because you might decide to do something that will be really
> slow. You can always hit ^C if you get tired of waiting. This seems
> like it's very far from being the most important thing for us to
> optimize, though of course it's great if we can.
>
Well FWIW, here's the quick-fix solution to make this median function
support window queries.
It's OK if all you want is one median per partition, but otherwise
it's pretty ugly. It will copy the entire tuplesort for each row in a
running median, which is horrible, but that's actually negligible
compared to the subsequent sort that's going to happen for that row.
I'd actually be tempted to force the tuplesort to stay in memory for a
running median, on the grounds that performance will be so bad for a
large partition that you'd run out of patience waiting for it long
before it would run out of memory :-(
Regards,
Dean
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
Attachment: median2.diff
Description: application/octet-stream (20.2 KB)
In response to
Responses
pgsql-hackers by date
Next :From: Dan PortsDate: 2010-10-04 19:22:32Subject : Re: MIT benchmarks pgsql multicore (up to 48)performancePrevious :From : Tomoaki SatoDate : 2010-10-04 19:18:04Subject : Re: pgadmin3_90 package
pgsql-rrreviewers by date
Next :From: Marko TiikkajaDate: 2010-10-04 21:59:07Subject : Re: [HACKERS] top-level DML under CTEsPrevious :From : Robert HaasDate : 2010-10-04 17:22:39Subject : Re: wip: functions median and percentile
