Re: [GENERAL] SELECT DISTINCT ON removes results

On 28 October 2016 at 21:39, Guyren Howe wrote: > Using 9.5, this query: > > SELECT o.id, >a.number AS awb > FROM pt.orders o > LEFT JOIN ( > SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, > ','::text))) > string_agg(air_way_bills.number::

Re: [GENERAL] SELECT DISTINCT ON removes results

On Fri, Oct 28, 2016 at 2:17 PM, Guyren Howe wrote: > On Oct 28, 2016, at 14:15 , Guyren Howe wrote: > > > On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: > > > ​On its face the statement "DISTINCT ON removes results" is not at all > surprising given its definition. > > > What is surpris

Re: [GENERAL] SELECT DISTINCT ON removes results

> On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: > > On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe > wrote: > Using 9.5, this query: Unless I'm missing something, this ought to be impossible. Two queries differing only in having a DISTINCT clause produce the s

Re: [GENERAL] SELECT DISTINCT ON removes results

On Oct 28, 2016, at 14:15 , Guyren Howe wrote: > > On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: >> >> ​On its face the statement "DISTINCT ON removes results" is not at all >> surprising given its definition. > > What is surprising is that it remov

Re: [GENERAL] SELECT DISTINCT ON removes results

On Oct 28, 2016, at 13:50 , David G. Johnston wrote: > > ​On its face the statement "DISTINCT ON removes results" is not at all > surprising given its definition. What is surprising is that it removes *all* results…

Re: [GENERAL] SELECT DISTINCT ON removes results

On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe wrote: > Using 9.5, this query: > > SELECT o.id, >a.number AS awb > FROM pt.orders o > LEFT JOIN ( > SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, > ','::text))) > string_agg(air_way_bills.numb

[GENERAL] SELECT DISTINCT ON removes results

Using 9.5, this query: SELECT o.id, a.number AS awb FROM pt.orders o LEFT JOIN ( SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, ','::text))) string_agg(air_way_bills.number::text, ','::text) AS number, air_way_bills.order_id

Re: [GENERAL] select distinct postgres 9.2

2016-09-19 9:18 GMT+12:00 Patrick B : > Hi guys, > > I've got the following query: > > WITH >> accounts AS ( >> SELECT >> c.id AS company_id, >> c.name_first AS c_first_name, >> c.name_last AS c_last_name, >> c.company AS c_name, >> FROM public.clients c >>

[GENERAL] select distinct postgres 9.2

Hi guys, I've got the following query: WITH > accounts AS ( > SELECT > c.id AS company_id, > c.name_first AS c_first_name, > c.name_last AS c_last_name, > c.company AS c_name, > FROM public.clients c > WHERE id = 33412393 > ORDER BY 1 LIMIT 100 >

Re: [GENERAL] select DISTINCT

Thank you Kevin and Jeff for the responses. These are very helpful. On Fri, Sep 6, 2013 at 10:48 PM, Jeff Janes wrote: > On Friday, September 6, 2013, pg noob wrote: > >> >> Hi all, >> >> I'm curious about some of the query estimates that I'm seeing with >> queries that use DISTINCT. >> I am us

Re: [GENERAL] select DISTINCT

On Friday, September 6, 2013, pg noob wrote: > > Hi all, > > I'm curious about some of the query estimates that I'm seeing with queries > that use DISTINCT. > I am using postgres 8.4.13 > > I did a couple of quick tests, and found that PostgreSQL seems to do some > expensive work to > return DISTI

Re: [GENERAL] select DISTINCT

pg noob wrote: > The GROUP BY performs much better than DISTINCT even though both > these two queries return the exact same count result. No, GROUP BY performs much better than count(DISTINCT colname). To confirm that this isn't something that has changed in the four years since 8.4 was release

[GENERAL] select DISTINCT

Hi all, I'm curious about some of the query estimates that I'm seeing with queries that use DISTINCT. I am using postgres 8.4.13 I did a couple of quick tests, and found that PostgreSQL seems to do some expensive work to return DISTINCT rows. This is contrary to what I was expecting because I ex

Re: [GENERAL] SELECT DISTINCT

point me to the appropriate developer guide.   Regards From: Tom Lane To: Kevin Grittner Cc: salah jubeh ; pgsql Sent: Friday, January 18, 2013 2:02 AM Subject: Re: [GENERAL] SELECT DISTINCT "Kevin Grittner" writes: > salah jubeh wrote: &

Re: [GENERAL] SELECT DISTINCT

"Kevin Grittner" writes: > salah jubeh wrote: >> I think any query that returns a unique column (primary key, >> unique) which is not duplicated in some way (join) can use this >> optimisation technique. > I agree that if the planner searched for that, there would be cases > where the DISTINCT ke

Re: [GENERAL] SELECT DISTINCT

salah jubeh wrote: > During my work, I have seen a common practice of using DISTINCT. > Some will argue that developer should know the effect of using > it, but keep in mind not all developers are gurus in RDBMs. "SELECT DISTINCT eliminates duplicate rows from the result." Personally, I would not

[GENERAL] SELECT DISTINCT

Hello Guys, During my work, I have seen a common practice of using DISTINCT . Some will argue that developer should know the effect of using it, but keep in mind not all developers are gurus in RDBMs. Normally, developers work in a narrow domain. Using DISTINCT might lead to a huge performance 

Re: [GENERAL] select distinct in a subquery bug/problem

On 8/11/2012 2:21 PM, Raymond O'Donnell wrote: On 11/08/2012 04:32, Dan Halbert wrote: 1. select count(t1_id) from t1 where t1_id not in (select distinct t1_id from t2 limit 1103) ==> 13357 [CORRECT result] 2. select count(t1_id) from t1 where t1_id not in (select distinct t1_id from t2 limi

Re: [GENERAL] select distinct in a subquery bug/problem

On 11/08/2012 04:32, Dan Halbert wrote: > In version 9.1.4-0ubuntu12.04: > > > > Hi - I am getting wrong answers from a certain kind of query, and have > narrowed it down to a change in the query plan between two similar > queries. The two queries below use different query plans, and generate >

Re: [GENERAL] select distinct in a subquery bug/problem

"Dan Halbert" writes: > 1. select count(t1_id) from t1 where t1_id not in (select distinct t1_id from > t2 limit 1103) ==> 13357 [CORRECT result] > 2. select count(t1_id) from t1 where t1_id not in (select distinct t1_id from > t2 limit 1104) ==> 0 [WRONG result; should be close to 13357]

[GENERAL] select distinct in a subquery bug/problem

In version 9.1.4-0ubuntu12.04: Hi - I am getting wrong answers from a certain kind of query, and have narrowed it down to a change in the query plan between two similar queries. The two queries below use different query plans, and generate different results, one of which is completely wrong.

Re: [GENERAL] select DISTINCT not ordering the returned rows

I totally agree with you and the problem is gonna be fixed. I just needed a temporary solution until the patch goes out. Thank you, Ioana --- On Wed, 3/2/11, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] select DISTINCT not ordering the returned rows > To: "Ioan

Re: [GENERAL] select DISTINCT not ordering the returned rows

Ioana Danes, 02.03.2011 21:35: Hi Everyone, I would like to ask for your help finding a temporary solution for my problem. I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows. The database is free to return rows in any order it thinks is most efficie

Re: [GENERAL] select DISTINCT not ordering the returned rows

Ioana Danes writes: > I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of > the returned rows. Your application is simply broken if it assumes that DISTINCT results in ordering the rows. The only thing that guarantees that is an ORDER BY. You could probably work around

Re: [GENERAL] select DISTINCT not ordering the returned rows

I found it: disabling enable_hashagg --- On Wed, 3/2/11, Ioana Danes wrote: > From: Ioana Danes > Subject: [GENERAL] select DISTINCT not ordering the returned rows > To: "PostgreSQL General" > Received: Wednesday, March 2, 2011, 3:35 PM > Hi Everyone, > > I

[GENERAL] select DISTINCT not ordering the returned rows

Hi Everyone, I would like to ask for your help finding a temporary solution for my problem. I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows. The following script is a simplification of my real case: create table tmp_1 (field1 integer, field2 intege

Re: [GENERAL] SELECT DISTINCT triggers sorting operation

You were absolutely right Tom. Rising work_mem did the trick! Many thanks :-) What's the best value for work_mem ? Gaëtan Le 7 févr. 2010 à 07:38, Tom Lane a écrit : > =?iso-8859-1?Q?Ga=EBtan_Allart?= writes: >> I'm experiencing an interesting issue with PostgreSQL 8.4.2-r2. >> I was running

Re: [GENERAL] SELECT DISTINCT triggers sorting operation

=?iso-8859-1?Q?Ga=EBtan_Allart?= writes: > I'm experiencing an interesting issue with PostgreSQL 8.4.2-r2. > I was running a pretty big database on another server (8.3) pretty fine. > When I moved it to this new server, behaviour changed. Did you ANALYZE the database after loading it? Are you su

Re: [GENERAL] SELECT DISTINCT triggers sorting operation

On Sat, Feb 6, 2010 at 3:49 PM, Gaëtan Allart wrote: > Hi everybody, > > I'm experiencing an interesting issue with PostgreSQL 8.4.2-r2. > I was running a pretty big database on another server (8.3) pretty fine. > When I moved it to this new server, behaviour changed. What does explain analyze ru

[GENERAL] SELECT DISTINCT triggers sorting operation

Hi everybody, I'm experiencing an interesting issue with PostgreSQL 8.4.2-r2. I was running a pretty big database on another server (8.3) pretty fine. When I moved it to this new server, behaviour changed. On a complex SELECT DISTINCT query, pgsql now runs a "SORT" operation although I've never r

Re: [GENERAL] SELECT DISTINCT very slow

On Fri, 2009-07-10 at 01:36 +0100, Greg Stark wrote: > Arguably the missing feature here is skip-scans where we scan the > index but only pull out one record for each distinct value. I'm not > sure there's anything particularly stopping Postgres from being able > to do them, but it might be a lot o

Re: [GENERAL] SELECT DISTINCT very slow

On Fri, Jul 10, 2009 at 1:41 PM, Ben Harper wrote: > > Unfortunately I can't use GROUP BY, because what I'm really doing is > SELECT DISTINCT ON(unique_field) id FROM table; You could do that using GROUP BY if you define a first() aggregate. In this case that would just be SELECT first(id) AS id f

Re: [GENERAL] SELECT DISTINCT very slow

Thanks for all the feedback. Using GROUP BY is indeed much faster (about 1 second). Unfortunately I can't use GROUP BY, because what I'm really doing is SELECT DISTINCT ON(unique_field) id FROM table; I'm not familiar with the Postgres internals, but in my own DB system that I have written, I do

Re: [GENERAL] SELECT DISTINCT very slow

Greg Stark writes: > Not really. The OP doesn't say how wide the record rows are but unless > they're very wide it wouldn't pay to use an index for this even if you > didn't have to access the heap also. It's going to be faster to scan > the whole heap and either sort or use a hash. Currently ther

Re: [GENERAL] SELECT DISTINCT very slow

On Thu, Jul 9, 2009 at 4:47 PM, Andres Freund wrote: > AFAIK the primary cause is that indexes in pg do not store visibility > information. Not really. The OP doesn't say how wide the record rows are but unless they're very wide it wouldn't pay to use an index for this even if you didn't have to a

Re: [GENERAL] SELECT DISTINCT very slow

Hello when you use older pg than 8.3, please, use GROUP BY. SELECT field FROM table GROUP BY field. Regards Pavel Stehule 2009/7/9 Ben Harper : > Hi, > Can anybody explain this: > > Records: 600,000 > Field Width: Approximately 20 UTF8 characters - type is VARCHAR(25) > Field is Indexed. > > SE

Re: [GENERAL] SELECT DISTINCT very slow

On Thursday 09 July 2009 17:09:13 Ben Harper wrote: > Hi, > Can anybody explain this: > > Records: 600,000 > Field Width: Approximately 20 UTF8 characters - type is VARCHAR(25) > Field is Indexed. > > SELECT DISTINCT field FROM table; > > Takes about 6 seconds. There are 111 distinct items. > > On

Re: [GENERAL] SELECT DISTINCT very slow

In response to Ben Harper : > Hi, > Can anybody explain this: > > Records: 600,000 > Field Width: Approximately 20 UTF8 characters - type is VARCHAR(25) > Field is Indexed. > > SELECT DISTINCT field FROM table; > > Takes about 6 seconds. There are 111 distinct items. What's the output of EXPLAI

[GENERAL] SELECT DISTINCT very slow

Hi, Can anybody explain this: Records: 600,000 Field Width: Approximately 20 UTF8 characters - type is VARCHAR(25) Field is Indexed. SELECT DISTINCT field FROM table; Takes about 6 seconds. There are 111 distinct items. On Sqlite, and another place where I have a B+Tree, this query is faster th

Re: [GENERAL] select distinct, index not used

Thank you Tom. The cron job for vacuum+analyze was not installed on the host. (I had this idea some seconds after posting) After vacuum+analyze the performance is good. I am happy. Nevertheless, on a different host with nearly the same data, a index scan is used. foo_hostone_foo=# explain analy

Re: [GENERAL] select distinct, index not used

Sam Mason writes: > On Thu, Apr 16, 2009 at 11:29:25AM -0400, Tom Lane wrote: >> , a full table indexscan isn't going to be particularly fast in >> any case; it's often the case that seqscan-and-sort is the right >> decision. > Is PG capable of "skipping" over duplicate values using an index? No

Re: [GENERAL] select distinct, index not used

On Thu, Apr 16, 2009 at 11:29:25AM -0400, Tom Lane wrote: > , a full table indexscan isn't going to be particularly fast in > any case; it's often the case that seqscan-and-sort is the right > decision. Is PG capable of "skipping" over duplicate values using an index? For example, if I've got a t

Re: [GENERAL] select distinct, index not used

Thomas Guettler writes: > why does the statement take so long? The column 'lieferant' is indexed. But > a sequential scan gets done. It might have something to do with the fact that the planner's idea of the size of the table is off by a factor of more than 100: > -> Seq Scan on foo_ab

Re: [GENERAL] select distinct, index not used

wasn't that improved now in 8.4, and before that on 8.3 ? still there are some funny things with distinct/group by . for instance, try select count(distinct foo) from bar; vs select count(1) from (select distinct foo from bar) f; :) I am not sure it pg was able to use index for that in 8.2. --

[GENERAL] select distinct, index not used

Hi, why does the statement take so long? The column 'lieferant' is indexed. But a sequential scan gets done. foo_egs_foo=# explain analyze SELECT DISTINCT "foo_abc_abc"."lieferant" FROM "foo_abc_abc"; QUERY PLAN --

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

Grzegorz Jaśkiewicz wrote: On Tue, Dec 9, 2008 at 9:02 AM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> wrote: or even, when you change bar to proper type - that is, timestamp SELECT distinct foo, min(bar) as minbar, max(bar) as maxbar FROM table WHERE bar < '2008-12-07 16:32:46' AND tbl_id=153 OR

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

Grzegorz Jaśkiewicz wrote: On Mon, Dec 8, 2008 at 10:19 PM, Madison Kelly <[EMAIL PROTECTED]> wrote: Hi all, I've got a table that I am trying to SELECT DISTINCT on one column and ORDER BY on a second column, but am getting the error: SELECT DISTINCT ON expressions must match initial ORDER BY

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

On Tue, Dec 9, 2008 at 9:02 AM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> wrote: > or even, when you change bar to proper type - that is, timestamp > > SELECT distinct foo, min(bar) as minbar, max(bar) as maxbar FROM > table WHERE bar < '2008-12-07 > 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

On Mon, Dec 8, 2008 at 10:19 PM, Madison Kelly <[EMAIL PROTECTED]> wrote: > Hi all, > > I've got a table that I am trying to SELECT DISTINCT on one column and > ORDER BY on a second column, but am getting the error: > > SELECT DISTINCT ON expressions must match initial ORDER BY expressions try SE

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

Adam Rich Wrote: > > > > > > When we get windowing functions, a lot of this pain will go away :) > > > > > > > Yes! Hope it won't be too long now. The patch seems to behave like it > > should > > now :) > > Hopefully we'll see it commited for 8.4. > > > > Though this does not look too much cleaner

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

> > > > When we get windowing functions, a lot of this pain will go away :) > > > > Yes! Hope it won't be too long now. The patch seems to behave like it > should > now :) > Hopefully we'll see it commited for 8.4. > > Though this does not look too much cleaner at least it's standard SQL: > > A

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

>>> Sent: 08 December 2008 22:19 > >>> To: pgsql-general@postgresql.org > >>> Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem > >>> > >>> Hi all, > >>> > >>>I've got a table that I am trying to SELECT D

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

> -Original Message- > From: David Fetter [mailto:[EMAIL PROTECTED] > Sent: 09 December 2008 00:55 > To: David Rowley > Cc: 'Madison Kelly'; pgsql-general@postgresql.org > Subject: Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem > > On Mon, Dec 0

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

David Fetter wrote: On Mon, Dec 08, 2008 at 11:16:29PM -, David Rowley wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Madison Kelly Sent: 08 December 2008 22:19 To: pgsql-general@postgresql.org Subject: [GENERAL] SELECT

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

On Mon, Dec 08, 2008 at 11:16:29PM -, David Rowley wrote: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:pgsql-general- > > [EMAIL PROTECTED] On Behalf Of Madison Kelly > > Sent: 08 December 2008 22:19 > > To: pgsql-general@postgresql.org

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

David Rowley wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Madison Kelly Sent: 08 December 2008 22:19 To: pgsql-general@postgresql.org Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem Hi all, I've got a table that

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of Madison Kelly > Sent: 08 December 2008 22:19 > To: pgsql-general@postgresql.org > Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem > > Hi all, > >

[GENERAL] SELECT DISTINCT ... ORDER BY problem

Hi all, I've got a table that I am trying to SELECT DISTINCT on one column and ORDER BY on a second column, but am getting the error: SELECT DISTINCT ON expressions must match initial ORDER BY expressions I can't add the second column to the DISTINCT clause because every row is unique. L

Re: [GENERAL] select distinct and index usage

Gregory Stark escribió: > I thought our DISTINCT index scan does do that but it still has to read the > index leaf pages sequentially. It doesn't back-track up the tree structure and > refind the next key. The way to back-track is to start the scan over from the root page down, keeping a stack of

Re: [GENERAL] select distinct and index usage

"Tom Lane" <[EMAIL PROTECTED]> writes: > Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Tom Lane escribió: >>> What I think you'll find, though, is that once you do force an indexscan >>> to be picked it'll be slower. Full-table index scans are typically >>> worse than seqscan+sort, unintuitive th

Re: [GENERAL] select distinct and index usage

On Mon, Apr 7, 2008 at 9:11 PM, Stephen Denne <[EMAIL PROTECTED]> wrote: > > You may be able to make use of an index by rearranging your query to > generate a series between your min & max values, testing whether each value > is in your table. > > You've got 4252 distinct values, but what is th

Re: [GENERAL] select distinct and index usage

On Sun, Apr 06, 2008 at 07:32:58PM -0400, David Wilson wrote: > I have a reasonably large table (~75m rows,~18gb) called "vals". It > includes an integer datestamp column with approximately 4000 unique > entries across the rows; there is a normal btree index on the > datestamp column. When I attemp

Re: [GENERAL] select distinct and index usage

Alban Hertroys wrote > Something that might help you, but I'm not sure whether it > might hurt > the performance of other queries, is to cluster that table on > val_datestamp_idx. That way the records are already (mostly) sorted > on disk in the order of the datestamps, which seems to be the

Re: [GENERAL] select distinct and index usage

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane escribió: >> What I think you'll find, though, is that once you do force an indexscan >> to be picked it'll be slower. Full-table index scans are typically >> worse than seqscan+sort, unintuitive though that may sound. > Hmm, should we switch

Re: [GENERAL] select distinct and index usage

Tom Lane escribió: > What I think you'll find, though, is that once you do force an indexscan > to be picked it'll be slower. Full-table index scans are typically > worse than seqscan+sort, unintuitive though that may sound. Hmm, should we switch the CLUSTER code to do that? -- Alvaro Herrera

Re: [GENERAL] select distinct and index usage

"David Wilson" <[EMAIL PROTECTED]> writes: > It appears to be doing a sequential scan regardless of the set, as if > it doesn't believe it can use the index for some reason More likely, it's getting a cost estimate for the indexscan that's so bad that it even exceeds the 1-unit thumb on th

Re: [GENERAL] select distinct and index usage

> Still doing the sequential scan on the table, but at least it's > avoiding the expensive disk merge sort. It still seems as if I ought > to be able to coax it into using an index for this type of query, > though- especially since it's using one on the other table. Is there > perhaps some way to r

Re: [GENERAL] select distinct and index usage

David Wilson escribió: > explain analyze select datestamp from vals group by datestamp; > QUERY > PLAN >

Re: [GENERAL] select distinct and index usage

On Mon, Apr 7, 2008 at 7:57 PM, Gregory Stark <[EMAIL PROTECTED]> wrote: > > You could try changing it to the equivalent GROUP BY query. The planner, > unfortunately, doesn't know they're equivalent and has two separate sets of > plans available. In this case where there are only 4,000 distinct

Re: [GENERAL] select distinct and index usage

"David Wilson" <[EMAIL PROTECTED]> writes: > I appreciate the responses so far! I'm used to several minutes for > some of the complex queries on this DB, but 12.5 minutes for a select > distinct just seems wrong. :) You could try changing it to the equivalent GROUP BY query. The planner, unfortun

Re: [GENERAL] select distinct and index usage

On Mon, Apr 7, 2008 at 1:42 PM, Alban Hertroys <[EMAIL PROTECTED]> wrote: > > Have you tried this query with enable_seqscan=off? If my guess is right > (and the planners, in that case) it'd be even slower. set enable_seqscan=off; explain select distinct datestamp from vals;

Re: [GENERAL] select distinct and index usage

On Mon, 7 Apr 2008 19:42:02 +0200 Alban Hertroys <[EMAIL PROTECTED]> wrote: > > explain analyze select distinct datestamp from vals; > >QUERY > > PLAN > > -- > >

Re: [GENERAL] select distinct and index usage

On Apr 7, 2008, at 9:47 AM, David Wilson wrote: On Mon, Apr 7, 2008 at 2:05 AM, Alban Hertroys <[EMAIL PROTECTED]> wrote: The databases estimates seem consistent with yours, so why is it doing this? Could you provide an EXPLAIN ANALYSE? It shows the actual numbers next to the estimates, alth

Re: [GENERAL] select distinct and index usage

On Mon, Apr 7, 2008 at 2:05 AM, Alban Hertroys <[EMAIL PROTECTED]> wrote: > On Apr 7, 2008, at 1:32 AM, David Wilson wrote: > > > > > The databases estimates seem consistent with yours, so why is it doing > this? Could you provide an EXPLAIN ANALYSE? It shows the actual numbers next > to the estim

Re: [GENERAL] select distinct and index usage

On Apr 7, 2008, at 1:32 AM, David Wilson wrote: I have a reasonably large table (~75m rows,~18gb) called "vals". It includes an integer datestamp column with approximately 4000 unique entries across the rows; there is a normal btree index on the datestamp column. When I attempt something like "se

Re: [GENERAL] select distinct and index usage

Both tables are vacuumed and analyzed. I have other queries that are using various indices on the vals table in an intelligent fashion. I can try increasing the stats, certainly, although they're at the defaults for both tables. The variation is definitely identical- the set of datestamps in the v

Re: [GENERAL] select distinct and index usage

David Wilson wrote: On another table in the same database with a much smaller number of total rows (~15m rows), I have the exact same situation- but in this case the index on the datestamp column *is* used: Have you run ANALYZE on both tables? It might be worth increasing the stats collected

[GENERAL] select distinct and index usage

I have a reasonably large table (~75m rows,~18gb) called "vals". It includes an integer datestamp column with approximately 4000 unique entries across the rows; there is a normal btree index on the datestamp column. When I attempt something like "select distinct datestamp from vals", however, expla

Re: [GENERAL] SELECT DISTINCT ON and ORDER BY

josep porres escreveu: maybe this? select value, max(id) as id, max(order_field) as order_field from mytable group by value order by 3 Wrong. For the op data you will obtain tuples not in original relation. bdteste=# SELECT * FROM foo; id | value | order_field +---+-

Re: [GENERAL] SELECT DISTINCT ON and ORDER BY

An: Stanislav Raskin Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] SELECT DISTINCT ON and ORDER BY maybe this? select value, max(id) as id, max(order_field) as order_field from mytable group by value order by 3 2008/3/28, Stanislav Raskin <[EMAIL PROTECTED]>: Hello eve

Re: [GENERAL] SELECT DISTINCT ON and ORDER BY

On Fri, 28 Mar 2008, Sam Mason <[EMAIL PROTECTED]> writes: > On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote: >> The result in this case should be: >> >> id value order_field >> 3 10 1 >> 5 12 2 >> 45 8 Yet another lame solution: test=# SELECT max(id) AS

Re: [GENERAL] SELECT DISTINCT ON and ORDER BY

maybe this? select value, max(id) as id, max(order_field) as order_field from mytable group by value order by 3 2008/3/28, Stanislav Raskin <[EMAIL PROTECTED]>: > > Hello everybody, > > > > I have a table like this one: > > > > id value order_field > > 1 103 > > 2

Re: [GENERAL] SELECT DISTINCT ON and ORDER BY

On Fri, 28 Mar 2008, Sam Mason <[EMAIL PROTECTED]> writes: > On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote: >> The result in this case should be: >> >> id value order_field >> 3 10 1 >> 5 12 2 >> 45 8 > > SELECT id, value > FROM ( > SELECT DISTINC

Re: [GENERAL] SELECT DISTINCT ON and ORDER BY

On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote: > id value order_field > 1 10 3 > 2 12 4 > 3 10 1 > 45 8 > 5 12 2 > > Hence selecting rows with distinct values, but primarily ordered by > order_field, instead of value, which is requires by DI

[GENERAL] SELECT DISTINCT ON and ORDER BY

Hello everybody, I have a table like this one: id value order_field 1 103 2 124 3 101 4 5 8 5 122 What I want to do, is to do something like SLECT DISTINCT ON (my_table.value) my_

Re: [GENERAL] SELECT DISTINCT performance issue

At 13:06 05/06/05, Tom Lane wrote: K C Lau <[EMAIL PROTECTED]> writes: > esdt=> explain analyze select DISTINCT ON (PlayerID) PlayerID,AtDate from > Player > where PlayerID='0' order by PlayerID desc, AtDate desc; > Unique (cost=0.00..2507.66 rows=1 width=23) (actual time=0.000..187.000

Re: [GENERAL] SELECT DISTINCT performance issue

K C Lau <[EMAIL PROTECTED]> writes: > esdt=> explain analyze select DISTINCT ON (PlayerID) PlayerID,AtDate from > Player > where PlayerID='0' order by PlayerID desc, AtDate desc; > Unique (cost=0.00..2507.66 rows=1 width=23) (actual time=0.000..187.000 > rows=1 loops=1) > -> Index S

[GENERAL] SELECT DISTINCT performance issue

Hi All, We are testing PostgreSQL 8.0.3 on MS Windows for porting an OLTP system from MS SqlServer. We got a major performance issue which seems to boil down to the following type of query: select DISTINCT ON (PlayerID) PlayerID,AtDate from Player where PlayerID='0' order by PlayerID

Re: [GENERAL] Select distinct sorting all rows 8.0rc1

Pierre-Frédéric Caillaud wrote: > The planner is smarter with GROUP BY than with DISTINCT, so you can > rewrite your query as the following, whihc will probaly use a > HashAggregate, and be a lot faster : > > SELECT service_id FROM five_min_stats_200408 GROUP BY service_id; Pierre-Fré

Re: [GENERAL] Select distinct sorting all rows 8.0rc1

The planner is smarter with GROUP BY than with DISTINCT, so you can rewrite your query as the following, whihc will probaly use a HashAggregate, and be a lot faster : SELECT service_id FROM five_min_stats_200408 GROUP BY service_id; This won't avoid the Seq Scan however. If y

[GENERAL] Select distinct sorting all rows 8.0rc1

System is 4-way Opteron 844, 16 GB memory, SCSI. This is a trial run for converting an Oracle DB, so system is not optimized. I have shared_buffers = 5 work_mem = 65536 A table has about 65 million rows (data collection system.) It has a primary key, no other indexes, no OIDs. The primar

Re: [GENERAL] select distinct w/order by

: [GENERAL] select distinct w/order by On Wed, 31 Mar 2004, Tom Lane wrote: > "John Liu" <[EMAIL PROTECTED]> writes: > > The original simple SQL - > > select distinct atcode from TMP order by torder; > > This is not "simple", it is "broken SQ

Re: [GENERAL] select distinct w/order by

ing is black or white, but it's not. The user has the choice at least. But when I use PostgreSQL, I need find an alternative solution to handle such issue. johnl -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 2:37 PM To: John Li

Re: [GENERAL] select distinct w/order by

2004 4:35 PM To: Tom Lane Cc: John Liu; [EMAIL PROTECTED] Subject: Re: [GENERAL] select distinct w/order by On Wed, 31 Mar 2004, Tom Lane wrote: > "John Liu" <[EMAIL PROTECTED]> writes: > > The original simple SQL - > > select distinct atcode from TMP order by torder

Re: [GENERAL] select distinct w/order by

On Wed, 31 Mar 2004, Tom Lane wrote: > "John Liu" <[EMAIL PROTECTED]> writes: > > The original simple SQL - > > select distinct atcode from TMP order by torder; > > This is not "simple", it is "broken SQL with an undefined result". > > If DISTINCT merges multiple rows with the same atcode, how a

Re: [GENERAL] select distinct w/order by

| | cc: | | Subject: Re: [GENERAL] select distinct w/order by | >

Re: [GENERAL] select distinct w/order by

On Wed, 31 Mar 2004, John Liu wrote: > I know this is an old topic, but it's not easy to find a way around it, so > when we migrate SQL from other database to PostgreSQL, it causes a huge > headache. Here's an extremely simple example - > > The original simple SQL - > select distinct atcode from T

Re: [GENERAL] select distinct w/order by

"John Liu" <[EMAIL PROTECTED]> writes: > The original simple SQL - > select distinct atcode from TMP order by torder; This is not "simple", it is "broken SQL with an undefined result". If DISTINCT merges multiple rows with the same atcode, how are we supposed to know which row's value of torder t

Re: [GENERAL] select distinct w/order by

On Wednesday 31 March 2004 18:50, John Liu wrote: > I know this is an old topic, but it's not easy to find a way around it, so > when we migrate SQL from other database to PostgreSQL, it causes a huge > headache. Here's an extremely simple example - > > The original simple SQL - > select distinct a

[GENERAL] SELECT DISTINCT ON... ORDER BY...

Did massive amounts of searching and read Tom Lane's post regarding the subject, but that was dated January of 1999.  Was wondering if anyone know if there was any progress on the issue and what the resulting outcome was.   Is there a way to select distinct on one column and sort by another?

  1   2   >