> Why did you use row_number instead of rank?
Because I assumed he only wanted one row in the event of ties.
Hmmm, although with that schema, there won't be ties. So it's pretty
much arbitrary then.
> I am now curious how the speed compares though. I still think the
> DISTINCT ON will be faste
On 02/24/2011 06:20 PM, Josh Berkus wrote:
SELECT id_key, time_stamp, value
FROM (
SELECT id_key, time_stamp, value,
row_number()
OVER ( PARTITION BY id_key
ORDER BY time_stamp DESC)
as ranking
FROM thetable
Friday, February 25, 2011, 12:53:08 AM you wrote:
> select distinct on (a,b,c)
> a, b c, time_stamp,value
Without the comma, you declare 'b AS c'
> from data
> order by a, b, c, time_stamp desc;
> The output produced is the same as this query:
> select distinct on (a,b)
> a
On 2/24/11 3:38 PM, Dave Crooke wrote:
> Thanks to all I had a tickling feeling at the back of my mind that
> there was a neater answer here. For the record, times (all from
> in-memory cached data, averaged over a bunch of runs):
>
> Dependent subquery = 117.9 seconds
> Join to temp table =
P.S. I noticed inadvertently (by making a typo ;-) that not all of the
columns in the DISTINCT ON are required to be part of the output, in which
case it appears to reduce the DISTINCT ON to the columns that are
represented in my real world situation, "id_key" is actually composed
of 3 columns
Thanks to all I had a tickling feeling at the back of my mind that
there was a neater answer here. For the record, times (all from in-memory
cached data, averaged over a bunch of runs):
Dependent subquery = 117.9 seconds
Join to temp table = 2.7 sec
DISTINCT ON = 2.7 sec
So the DISTINCT ON m
On Thu, Feb 24, 2011 at 2:18 PM, Kevin Grittner
wrote:
> Dave Crooke wrote:
>
>> create table data
>> (id_key int,
>> time_stamp timestamp without time zone,
>> value double precision);
>>
>> create unique index data_idx on data (id_key, time_stamp);
>
>> I need to find the most recent
On Thu, Feb 24, 2011 at 12:59 PM, Vik Reykja wrote:
> On Thu, Feb 24, 2011 at 20:56, Dave Johansen wrote:
>
>> On Thu, Feb 24, 2011 at 12:33 PM, Vik Reykja wrote:
>>
>>> On Thu, Feb 24, 2011 at 16:14, Dave Johansen wrote:
>>>
You'll notice that the subquery version is doing the full join an
I'm using PostgreSQL 8.3.3 and I have a view that does a UNION ALL on
two joins and it doesn't seem to want to push the IN (subquery)
optimization down into the plan for the two queries being unioned. Is
there something I can do to fix this? Or is it just a limitation of
the planner/optimizer?
I a
Michael Glaesemann wrote:
> SELECT DISTINCT ON (data.id_key)
>data.id_key, data.time_stamp, data.value
> FROM data
> ORDER BY data.id_key, data.time_stamp DESC;
Dang! I forgot the DESC in my post! Thanks for showing the
*correct* version.
-Kevin
--
Sent via pgsql-performance
On Feb 24, 2011, at 14:55, Dave Crooke wrote:
> Is there a more elegant way to write this, perhaps using PG-specific
> extensions?
SELECT DISTINCT ON (data.id_key)
data.id_key, data.time_stamp, data.value
FROM data
ORDER BY data.id_key, data.time_stamp DESC;
Michael Glaesemann
grzm s
Dave Crooke wrote:
> create table data
>(id_key int,
> time_stamp timestamp without time zone,
> value double precision);
>
> create unique index data_idx on data (id_key, time_stamp);
> I need to find the most recent value for each distinct value of
> id_key.
Well, unless you u
On Thu, Feb 24, 2011 at 1:55 PM, Dave Crooke wrote:
> Hi foks
>
> This is an old chestnut which I've found a number of online threads for, and
> never seen a clever answer to. It seems a common enough idiom that there
> might be some slicker way to do it, so I thought I might inquire with this
> a
On Thu, Feb 24, 2011 at 20:56, Dave Johansen wrote:
> On Thu, Feb 24, 2011 at 12:33 PM, Vik Reykja wrote:
>
>> On Thu, Feb 24, 2011 at 16:14, Dave Johansen wrote:
>>
>>> You'll notice that the subquery version is doing the full join and then
>>> the filtering, but the explicitly listed version p
On Thu, Feb 24, 2011 at 12:33 PM, Vik Reykja wrote:
> On Thu, Feb 24, 2011 at 16:14, Dave Johansen wrote:
>
>> You'll notice that the subquery version is doing the full join and then
>> the filtering, but the explicitly listed version pushing the filtering into
>> the plan before the join. Is the
Hi foks
This is an old chestnut which I've found a number of online threads for, and
never seen a clever answer to. It seems a common enough idiom that there
might be some slicker way to do it, so I thought I might inquire with this
august group if such a clever answer exists
Consider the fo
On 02/24/2011 12:13 PM, Greg Smith wrote:
That's not quite the right filter. You want to screen out
everything that isn't a unique index, not just the primary key ones.
You probably can't drop any of those without impacting database
integrity.
Ah yes. I was considering adding the clause for u
On Thu, Feb 24, 2011 at 16:14, Dave Johansen wrote:
> You'll notice that the subquery version is doing the full join and then the
> filtering, but the explicitly listed version pushing the filtering into the
> plan before the join. Is there a way to make the subquery version perform
> the same op
Shaun Thomas wrote:
I noticed with our database that without the indisprimary clause, we
had another 4GB of unused indexes.
That's not quite the right filter. You want to screen out everything
that isn't a unique index, not just the primary key ones. You probably
can't drop any of those wit
On Thu, Feb 24, 2011 at 8:14 AM, Dave Johansen wrote:
> I'm using PostgreSQL 8.3.3 and I have a view that does a UNION ALL on two
> joins and it doesn't seem to want to push the IN (subquery) optimization
> down into the plan for the two queries being unioned. Is there something I
> can do to fix
Merlin Moncure writes:
> Your entire function could probably be reduced to one SQL expression
> with some thought.
Or if not that, at least try to get rid of the use of varchar. All
those forced varchar-to-date-and-back conversions are expensive.
I'm also more than a tad worried by this:
>
On Thu, Feb 24, 2011 at 3:52 AM, Gnanakumar wrote:
> Hi,
>
> We're using PostgreSQL v8.2.3 on RHEL5.
>
> I'm developing a PostgreSQL plpgsql function for one of our application
> report. When I try to run the function multiple times (even twice or
> thrice), I'm seeing considerable amount of memo
I'm using PostgreSQL 8.3.3 and I have a view that does a UNION ALL on two
joins and it doesn't seem to want to push the IN (subquery) optimization
down into the plan for the two queries being unioned. Is there something I
can do to fix this? Or is it just a limitation of the planner/optimizer?
I a
On 02/23/2011 03:17 PM, Greg Smith wrote:
Yes. The block usage you're seeing there reflects the activity from
maintaining the index. But since it isn't ever being used for
queries, with zero scans and zero rows it's delivered to clients,
Nice to know. To that end, here's a query that will find
"Gnanakumar" wrote:
> We're using PostgreSQL v8.2.3 on RHEL5.
http://www.postgresql.org/support/versioning
The 8.2 release is up to 8.2.20:
http://www.postgresql.org/
By the way, 8.2 is scheduled to go out of support later this year:
http://wiki.postgresql.org/wiki/PostgreSQL_Release_S
Hello
It hard to say where can be a problem
I see a some risks
a) v8.2.3 isn't last version of 8.2 line
b) why you use a varchar data type for v_loopingdate,
v_cur_start_date, v_cur_end_date - it's bad idea? - you have to do
cast between date and varchar - all operation are slower and needs
more
Hi,
We're using PostgreSQL v8.2.3 on RHEL5.
I'm developing a PostgreSQL plpgsql function for one of our application
report. When I try to run the function multiple times (even twice or
thrice), I'm seeing considerable amount of memory being taken up by
PostgreSQL and thereby after sometime, comp
On Wed, Feb 23, 2011 at 10:37 PM, Tom Lane wrote:
> Merlin Moncure writes:
> > you can always do this:
>
> > create index performance_creation_date_desc_idx on
> > performance(creation_date desc nulls last);
>
> > which will index optimize your sql. Interesting that 'null last'
> > fools disall
I have created two tables. In the first table i am using many fields to
store 3 address.
as well as in b table, i am using array data type to store 3 address.
is
there any issue would face in performance related things which one
will
cause the performance issue.
The array is interest
29 matches
Mail list logo
