Re: Feature-Request: Performance-Optimization when using limit in combination with order by on querys using union

Karsten P writes: > i'm sorry i didn't check that first. it just won't work in my real-life > example. > though each part of the query is using an index-scan it is than using a > 'normal' append > instead of a merge-append, but i don't know why. The "Subquery Scan" nodes shown in your real-life

Re: Feature-Request: Performance-Optimization when using limit in combination with order by on querys using union

On Thu, 8 May 2025 at 22:57, Karsten P wrote: > i'm sorry i didn't check that first. it just won't work in my real-life > example. > though each part of the query is using an index-scan it is than using a > 'normal' append > instead of a merge-append, but i don't know why. You could try: SET ena

Re: Feature-Request: Performance-Optimization when using limit in combination with order by on querys using union

r' -> varchar   - column 'invoice_date' -> timestamp with index on invoice_date and many records in both if them. now we have a view combining both of them as create view documents as (     select order_number as document_number, order_date as document_date from orders     union

Feature-Request: Performance-Optimization when using limit in combination with order by on querys using union

eate view documents as (     select order_number as document_number, order_date as document_date from orders     union all select invoice_number, invoice_date from invoices ) finding the last order placed in the database ist really easy:   select order_number from orders order by order_date desc

Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY

On Tue, 6 Feb 2024 at 01:23, Sean v wrote: > SELECT "orders".* > FROM "orders" > WHERE (user_id IN ?, ?, ?) > ORDER BY "orders"."created_at" LIMIT 50 > > I have two indexes - `(user_id)` and `(user_id, created_at)`. Only the first > in

Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY

t; query planner yet. > > I know I can get it to do precisely this if I use a CROSS JOIN LATERAL: > > SELECT o.*FROM company_users cuCROSS JOIN LATERAL ( >SELECT * >FROM orders o > WHERE o.user_id = company_users.user_id >ORDER BY created_at DESC LIMIT 50 &g

Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY

On Mon, Feb 5, 2024 at 8:55 AM Ron Johnson wrote: > > Who knows which users are going to be in that list??? > > It doesn't matter. Worse case scenario there is only one user in the result and so all 50 rows are their earliest 50 rows. The system will thus never need more than the earliest 50 ro

Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY

ry like this: > > SELECT "orders".* > > FROM "orders" > > WHERE (user_id IN ?, ?, ?) > > ORDER BY "orders"."created_at" LIMIT 50 > [snip] > So my question is twofold: > - why doesn't Postgres use the composite index, and then r

Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY

; WHERE (user_id IN ?, ?, ?) ORDER BY "orders"."created_at" LIMIT 50 I have two indexes - `(user_id)` and `(user_id, created_at)`. Only the first index is ever used with this query. If I query for a specific user_id, as expected it uses the composite index. If I use an inner select

GIST index and ORDER BY

too low) To workaround the lack of GIST support for ORDER BY and implement paging (ie. TOP n) I use the following trick: SELECT … WHERE customer_id = $cust AND operation_date = $date AND operation_id < $id AND …other criteria ORDER BY (‘2200-01-01' <-> operation_date

Re: Reproducing incorrect order with order by in a subquery

Am Thu, Jun 15, 2023 at 12:58:55AM +0300 schrieb Ruslan Zakirov: e.g. > https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ ... > My goal was to find a small dataset that demonstrates this ordering > mismatch. I attempted to think it through whether it is even *pos

Re: Reproducing incorrect order with order by in a subquery

On Wed, Jun 14, 2023 at 3:50 PM Karsten Hilbert wrote: > IOW neither is help to be expected on this list nor can any > testing (on PG) help with anything to be expected on MySQL ? > Don't expect any help on mysql part. As to the question: since the outer query does not have an

Re: Reproducing incorrect order with order by in a subquery

ing (on PG) help with anything to be expected on MySQL ? As to the question: since the outer query does not have an ORDER BY it can return results in any order INCLUDING the one produced by the subquery. Which renders impossible any *proving* that it can return rows in orders different from the subquery *

Re: Reproducing incorrect order with order by in a subquery

On 6/14/23 05:03, Ruslan Zakirov wrote: [snip] Anyway, yesterday I tried my simplified case on Pg latest, Pg 11 and on mysql latest. Had no luck. Either my test case is too simple or I can not find the correct distribution of data between two tables. This is when you need a prod copy... -- Bo

Re: Reproducing incorrect order with order by in a subquery

On Tue, Jun 13, 2023 at 8:55 PM Ruslan Zakirov wrote: > On Tue, Jun 13, 2023 at 6:06 PM Tom Lane wrote: > >> Ruslan Zakirov writes: >> > I know how to fix the problem and I know that ORDER BY should be in the >> > outermost select. >> >> > However

Re: Reproducing incorrect order with order by in a subquery

.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN > ('cc', 'requestor') ) OR ( main.Owner = '38' ) ) >AND (main.IsMerged IS NULL) >AND (main.Status != 'deleted') >AND (main.Type = 'ticket') >AND (main.Owner = &#x

Re: Reproducing incorrect order with order by in a subquery

On Tue, Jun 13, 2023 at 6:06 PM Tom Lane wrote: > Ruslan Zakirov writes: > > I know how to fix the problem and I know that ORDER BY should be in the > > outermost select. > > > However, I want to write a test case that shows that the old code is > wrong, > > b

Re: Reproducing incorrect order with order by in a subquery

Ruslan Zakirov writes: > I know how to fix the problem and I know that ORDER BY should be in the > outermost select. > However, I want to write a test case that shows that the old code is wrong, > but can not create > minimal set of tables to reproduce it. With this I'm lo

Re: Reproducing incorrect order with order by in a subquery

On Tue, Jun 13, 2023 at 1:26 PM Thomas Kellerer wrote: > Ruslan Zakirov schrieb am 13.06.2023 um 09:49: > > For example I have a query: > > > > SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X) > > main; > > > > So the `ORDER BY` cla

Re: Reproducing incorrect order with order by in a subquery

Ruslan Zakirov schrieb am 13.06.2023 um 09:49: > For example I have a query: > > SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X) > main; > > So the `ORDER BY` clause ended up in a subquery. Most of the time > ordering works until it doesn't. > &

Reproducing incorrect order with order by in a subquery

Hello, For example I have a query: SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X) main; So the `ORDER BY` clause ended up in a subquery. Most of the time ordering works until it doesn't. Can you help me create a set of test tables with some data to reproduce this pr

Re: order by

something different within the *b-tree operator* class of varchar"? > After all, you only care about b-trees when you have b-tree indices. > > to my understanding, the btree operator is the default operator used to do any sort, like an order by, for varchar, text, .. types. > -- > Born in Arizona, moved to Babylonia. >

Re: order by

On Thu, May 11, 2023 at 11:30 AM Marc Millas wrote: > On Thu, May 11, 2023 at 5:23 PM Adrian Klaver > wrote: > >> On 5/11/23 08:00, Marc Millas wrote: >> > >> > On Thu, May 11, 2023 at 4:43 PM Adrian Klaver < >> adrian.kla...@aklaver.com >> > > wrote: >> > >> >

Re: order by

prod if I do select col_a, count(col_a) from table_a group by col_a order by col_a desc, I get the numbers of NULL on top. To get the number of NULL on top on the test db, I have to select col_a, count(col_a) from table_a group by col_a order by col_a asc. This doesn't answer your question

Re: order by

On 5/11/23 09:55, Marc Millas wrote: Thanks, I do know about index options. that table have NO (zero) indexes. If the table has no indices, then why did you write "it looks like there is something different within the *b-tree operator* class of varchar"?  After all, you only care about b-tr

Re: order by

change the behaviour of the ORDER BY clause ?? What I am saying is we need context. You are there and know what you are looking at and how it got there, we don't. At this point I don't know anything as I don't know the data operations involved. So how did the data get from the th

Re: order by

getting from the third database to the prod and test > clusters? > > For the machines hosting the third db, the prod and test clusters what > are?: > should I understand that you suggest that the way the data is inserted Do change the behaviour of the ORDER BY clause ?? > >

Re: order by

On 5/11/23 08:00, Marc Millas wrote: On Thu, May 11, 2023 at 4:43 PM Adrian Klaver > wrote: On 5/11/23 07:29, Marc Millas wrote: > Hi, > > I keep on investigating on the "death postgres" subject > but open a new thread as I don't know i

Re: order by

t another DB. > those 2 tables have no indexes. they are used to build kind of aggregates > thru multiple left joins. > > Do you mean you are doing dump/restore between them? > no > > Postgres version for each cluster is? > 14.2 > > > > > On prod if I do &

Re: order by

ut open a new thread as I don't know if it's related to my pb. >> >> I have 2 different clusters, on 2 different machines, one is prod, the >> second test. >> Same data volumes. >> >> On prod if I do >> select col_a, count(col_a) from table_a grou

Re: order by

stigating on the "death postgres" subject > but open a new thread as I don't know if it's related to my pb. > > I have 2 different clusters, on 2 different machines, one is prod, the > second test. > Same data volumes. > > On prod if I do > select col_a, co

Re: order by

can they be sharing the same data 'volume'? Do you mean you are doing dump/restore between them? Postgres version for each cluster is? On prod if I do select col_a, count(col_a) from table_a group by col_a order by col_a desc, I get the numbers of NULL on top. To get the number of NULL

order by

rom table_a group by col_a order by col_a desc, I get the numbers of NULL on top. To get the number of NULL on top on the test db, I have to select col_a, count(col_a) from table_a group by col_a order by col_a asc. so, it looks like there is something different within the b-tree operator class of v

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y

o also not at my best. >That should have read: > >>> select jsonb_build_object('opening_times’, >>> jsonb_agg(obj >>> ORDER BY >>> obj->>'weekday’, >>> obj->>'from_hour’, >>> obj->&

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y

> On 3 Mar 2023, at 20:32, Thorsten Glaser wrote: > > On Fri, 3 Mar 2023, Alban Hertroys wrote: > >> You can rewrite that into something like this: >> >> select jsonb_build_object('opening_times’, >> obj >> ORDER BY >>

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y

On Fri, 3 Mar 2023, Alban Hertroys wrote: >You can rewrite that into something like this: > >select jsonb_build_object('opening_times’, > obj > ORDER BY > obj->>'weekday’, > obj->>'from_hour’, > obj->

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y

yet worked with lateral JOINs.) You posted this bit: > jsonb_build_object('opening_times', > jsonb_agg(DISTINCT jsonb_build_object( > 'weekday', cot.weekday, > 'from_hour', cot.from_hour, >

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y

'afternoon', cgwa.afternoon, 'evening', cgwa.evening) AS obj FROM core_generalworkavailability cgwa ), -- … same for opening times SELECT cp.email, …, -- … jsonb_build_object('possible_work_times', COALESCE(

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y

27;weekday', cot.weekday, > 'from_hour', cot.from_hour, > 'to_hour', cot.to_hour) > ORDER BY > jsonb_build_object( > 'weekday', cot.weekday, > 'from_hour', cot.from_

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y

not as simple as you wrote. More like: jsonb_build_object('opening_times', jsonb_agg(DISTINCT jsonb_build_object( 'weekday', cot.weekday, 'from_hour', cot.from_hour, 'to_hour', co

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

#x27;subtwo_arr', subtwo_arr) from base join lateral (select json_agg(jsonb_build_object('key', subone_value) order by subone_value) from subone where subone.base_id = base.base_id) as so (subone_arr) on true join (select base_id, json_agg(subtwo_value order by subtwo_value des

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

Thorsten Glaser writes: > On Mon, 27 Feb 2023, Tom Lane wrote: >> Well, yeah. Simplify it to >> SELECT array_agg(DISTINCT x ORDER BY y) FROM mytable; > That’s… a bit too simple for this case. Sure, I was just trying to explain the rule. >> For the specific example you

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

On Mon, 27 Feb 2023, Tom Lane wrote: >Well, yeah. Simplify it to > > SELECT array_agg(DISTINCT x ORDER BY y) FROM mytable; That’s… a bit too simple for this case. >If there are several rows containing the same value of x and different >values of y, which y value are we suppo

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

build up json from scratch where every > individual component needs to be aware and take action. i.e., get rid of > the ORDER BY also. Maybe this belongs in an application layer with tooling > that already provides this capability. > > Just to clarify/correct myself - if building up j

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

On Mon, Feb 27, 2023 at 4:11 PM mirabilos wrote: > > jsonb_build_object('opening_times', > jsonb_agg(DISTINCT jsonb_build_object( > 'weekday', cot.weekday, > 'from_hour', cot.from_hour, > 'to_hour', cot.to_ho

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

mirabilos writes: > This works well. However, what I seem to be not allowed to do is > (without the extra COALESCE, to simplify): > ... > This is because, when I use DISTINCT (but only then‽), the ORDER BY > arguments must be… arguments to the function, or something. Well, yeah.

DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

jsonb_agg(DISTINCT ap.name ORDER BY ap.name)) … to get a distinct, sorted, list of “user permissions” from a table ap which I joined to the user table which is the main subject of the query. (For some reason I need to add DISTINCT because else duplica‐ tes are shown.) Wrapping this as… jsonb_bu

Re: A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

On Sun, Feb 19, 2023 at 4:18 PM Tom Lane wrote: > Kirk Wolak writes: > > I have some converted code that uses this syntax. > > Seems kinda dumb, but ... > > > The solution is to remove the ORDER BY NULL. [since that is not > > sortable, should it be ignored

Re: A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

the trouble though. > I've not looked at what the comment mentions > about RANGE OFFSET. Assuming we'd need to not remove any ORDER BY > clauses when the WindowClause is doing that. There's some Asserts in nodeWindowAgg.c about ordNumCols being positive, probably related. regards, tom lane

Re: A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

it doesn't seem worth it. Doing an if (IsA(tle->expr, Const)) continue; seems pretty simple and low-cost. I've not looked at what the comment mentions about RANGE OFFSET. Assuming we'd need to not remove any ORDER BY clauses when the WindowClause is doing that. > Getting r

Re: A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

po 20. 2. 2023 v 0:26 odesílatel David Rowley napsal: > On Mon, 20 Feb 2023 at 10:18, Tom Lane wrote: > > I don't see it at all. Comparing your two test queries on released > > branches, I see maybe 2x penalty for the ORDER BY NULL, not 30x. > > (In HEAD there

Re: A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

David Rowley writes: > I suspect ed1a88dda would be what made this faster in master. We'll > check for peer rows to check "NULL IS NOT DISTINCT FROM NULL" prior to > that change with the ORDER BY NULL query. Mmm, yeah, probably so: "order by null rows between unboun

Re: A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

On Mon, 20 Feb 2023 at 10:18, Tom Lane wrote: > I don't see it at all. Comparing your two test queries on released > branches, I see maybe 2x penalty for the ORDER BY NULL, not 30x. > (In HEAD there's only about 13% penalty.) I wonder what PG version > you are testing

Re: A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

Kirk Wolak writes: > I have some converted code that uses this syntax. Seems kinda dumb, but ... > The solution is to remove the ORDER BY NULL. [since that is not > sortable, should it be ignored?] > This does NOT SHOW UP with 1 million rows. I don't see it at all. C

A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

Okay, I have some converted code that uses this syntax. For 20 Million rows it was taking 15-20 minutes! (versus 3 minutes) on live data. See here: https://explain.depesz.com/s/VQFJ [There are 2 optimizations, removing the ORDER BY NULL, and just using a sequence] (The above is a live

Re: Order by in a sub query when aggregating the main query

I've changed the code to use order by in the aggregate and it seems there are no noticeable changes in the query performance. Thanks for the help. Best, Federico Caselli On Sun, 25 Sept 2022 at 00:30, Federico wrote: > > Understood, thanks for the explanation. > I'll w

Re: Order by in a sub query when aggregating the main query

Understood, thanks for the explanation. I'll work on updating the queries used by sqlalchemy to do array_agg(x order by x) instead of the order by in the subquery. > I think that right now that'd > incur additional sorting overhead, which is annoying. But work is > ongoing to

Re: Order by in a sub query when aggregating the main query

Federico writes: > A basic example of the type of query in question is the following (see > below for the actual query): > select w, array_agg(x) > from ( > select v, v / 10 as w > from pg_catalog.generate_series(25, 0, -1) as t(v) > order by v >

Order by in a sub query when aggregating the main query

Hi all, I have a question related to the order by clause used in a subquery of a main query that uses one or more aggregation functions with a group by. A basic example of the type of query in question is the following (see below for the actual query): select w, array_agg(x) from

Re: first order by then partition by x < a fixed value.

600 6 Joanne > > Idea is fixed order by id then cumulative sum. if <=600 then grouped > together using the same row_number. > > But I imagine this kind question has been solved many times. > Current posted solution uses a recursive query, which is really hard for >

first order by then partition by x < a fixed value.

anne Then expected output: account group size idname 1 100 1 John 1 2002 Mary 1 3003 Jane 2 4004 Anne2 1005 Mike 3 6006 Joanne Idea is fixed order by id then cumulative sum. if <=600 then grouped together using t

Re: Tuning a query with ORDER BY and LIMIT

On 2022-06-22 23:10:25 -0400, Jeff Janes wrote: > On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer wrote: > >That's just how btree indexes work and Oracle will have the same > >limitation. What would be possible is to use an index only scan > >(returning 2,634,718 matching results), sor

Re: Tuning a query with ORDER BY and LIMIT

d from each branch other than the one returning the last row. I suspect this may be what Oracle is doing. With some trickery, you can get PostgreSQL to do the same thing. (select * from foo where a=4 order by b) union all (select * from foo where a=7 order by b) order by b l

Re: Tuning a query with ORDER BY and LIMIT

uery like this since the index is on the 3 columns > matching the WHERE clause and the ORDER BY clause is in the 4th position > Oracle would be able to scan that index and as soon as it finds the first > matching 50 rows. > Can you show the plan from Oracle? I'm not an expert

Re: Tuning a query with ORDER BY and LIMIT

verbose, costs, buffers, timing, summary, hashes) >> select * from workflow_execution >> where workflow_id = 14560 and >> status = 'COMPLETED' and >> result = 'SUCCEEDED' >> order by completed_datetime desc limit 50; > This query should actuall

Re: Tuning a query with ORDER BY and LIMIT

On 2022-06-23 00:19:19 +0200, Peter J. Holzer wrote: > On 2022-06-22 23:48:37 +0200, Peter J. Holzer wrote: > > The index cannot be used for sorting, since the column used for sorting > > isn't in the first position in the index. > > compared to a single value ^ not E-Mail really needs a "Super

Re: Tuning a query with ORDER BY and LIMIT

'COMPLETED' and > > result in > > ('SUCCEEDED','REEXECUTED','ABORTED','DISCONTINUED','FAILED','PARTIAL_SUCCESS') > > order by completed_datetime desc limit 50; > [...] > The index cannot be used for sor

Re: Tuning a query with ORDER BY and LIMIT

; select * from workflow_execution > where workflow_id = 14560 and > status = 'COMPLETED' and > result = 'SUCCEEDED' > order by completed_datetime desc limit 50; This query should actually be able to use the index, since the first columns in the index are all

Re: Tuning a query with ORDER BY and LIMIT

e desc); > > explain (analyze, verbose, costs, buffers, timing, summary, hashes) > select * from workflow_execution > where workflow_id = 14560 and > status = 'COMPLETED' and > result in > ('SUCCEEDED','REEXECUTED','ABORTED',&

Re: Tuning a query with ORDER BY and LIMIT

etime desc); > > > > > > explain (analyze, verbose, costs, buffers, timing, summary, hashes) > > select * from workflow_execution > > where workflow_id = 14560 and > > status = 'COMPLETED' and > > result in > ('SUCCE

Tuning a query with ORDER BY and LIMIT

, completed_datetime desc); explain (analyze, verbose, costs, buffers, timing, summary, hashes) select * from workflow_execution where workflow_id = 14560 and status = 'COMPLETED' and result in ('SUCCEEDED','REEXECUTED','ABORTED','DISCO

Re: Casting a collation in an ORDER BY ... COLLATE

Kip Cole writes: > cldr_sql=# select * from models order by name collate > 'en-x-icu'::regcollation; > ERROR: syntax error at or near "'en-x-icu'" > LINE 1: select * from models order by name collate 'en-x-icu'::regco… You've got the sy

Re: Casting a collation in an ORDER BY ... COLLATE

On Sat, May 21, 2022 at 4:38 PM Kip Cole wrote: > I’m trying to cast a string value to a collation name without success. My > suspicion is this isn’t possible > based upon the error message. You cannot cast between a token that interpreted as a literal and one that is interpreted as a name. Fo

Casting a collation in an ORDER BY ... COLLATE

I’m trying to cast a string value to a collation name without success. My suspicion is this isn’t possible based upon the error message. But perhaps wiser heads than mine have a suggestion on how to do this? cldr_sql=# select * from models order by name collate 'en-x-icu'::regcollat

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

s PK, so scanning it for the first id should > be fast. >#explain analyze SELECT "shipment_import_records".* FROM >shipment_import_records" WHERE >shipment_import_records"."shipment_import_id" = 50906

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

On 12/6/21 22:16, Laurenz Albe wrote: An ugly alternative is to use "ORDER BY id + 0", which prevents PostgreSQL from using the index. That was actually the earliest form of Oracle hints. I remember doing exactly that in Oracle 5.1.22 on VAX/VMS. -- Mladen Gogala Database Cons

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

gt; > "shipment_import_records" WHERE > > > "shipment_import_records"."shipment_import_id" = 5090609 ORDER BY > > > "shipment_import_records

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

On 12/6/21 11:02 AM, Alan Hodgson wrote: On Mon, 2021-12-06 at 10:19 -0700, Rob Sargent wrote: To be clear, is it the devs or the ORM that's adding the ORDER  and the LIMIT?  I'm betting on devs.  Do they need the smallest id (first occurrance?) or do they need data common to all 5096 entries (N

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

happily uses without the LIMIT specifically. Yet with it > the query planner will always do something like: > > > > # explain SELECT "shipment_import_records".* FROM > "shipment_import_records" WHERE > "shipment_import_records"."shipme

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

On 12/6/21 10:02, Alan Hodgson wrote: On Mon, 2021-12-06 at 10:19 -0700, Rob Sargent wrote: To be clear, is it the devs or the ORM that's adding the ORDER  and the LIMIT?  I'm betting on devs.  Do they need the smallest id (first occurrance?) or do they need data common to all 5096 entries (Name

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

On Mon, 2021-12-06 at 10:19 -0700, Rob Sargent wrote: > To be clear, is it the devs or the ORM that's adding the ORDER  and > the  > LIMIT?  I'm betting on devs.  Do they need the smallest id (first > occurrance?) or do they need data common to all 5096 entries > (Name?) and > any record will do?

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

; be fast.    #explain analyze SELECT "shipment_import_records".* FROM     shipment_import_records" WHERE     shipment_import_records"."shipment_import_id" = 5090609 O

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

shipment_import_records"."shipment_import_id" = 5090609 ORDER BY "shipment_import_records"."id" ASC LIMIT 1; - - ---

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

planner will always do something like: > > # explain SELECT "shipment_import_records".* FROM "shipment_import_records" > WHERE "shipment_import_records"."shipment_import_id" = 5090609 ORDER BY > "shipment_import_records"."id&quo

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

On 12/6/21 10:03 AM, Alan Hodgson wrote: I keep running into problems like these: Devs are using an ORM. It really likes to produce queries like: SELECT "shipment_import_records".* FROM "shipment_import_records" WHERE shipment_import_records"."shipment

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

What is your default_statistics_target and how accurate is that estimate of 5668 rows? What is random_page_cost set to by the way? More importantly, what is the better plan that you'd like the planner to use with your existing indexes? It would seem logical to me to scan for the matching shipment_

Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

I keep running into problems like these: Devs are using an ORM. It really likes to produce queries like: SELECT "shipment_import_records".* FROM "shipment_import_records" WHERE shipment_import_records"."shipment_import_id" = 5090609 ORDER BY "shipment_im

Re: [E] Re: string_agg distinct order by

| unit as descr, len*case unit when 'mm' then 1 when 'cm' then 10 when 'm' then 1000 end as len_mm from items; ...I now want to have a list of all distinct descr ordered by length. But... select string_agg(descr,',' order by len_mm) from vu_items; ...cr

Re: string_agg distinct order by

a little to demonstrate- CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); CREATE TABLE person ( name text, current_mood mood ); INSERT INTO person VALUES ('Moe', 'happy'); INSERT INTO person VALUES ('Joe', 'sad'); I

Re: string_agg distinct order by

"Markhof, Ingolf" writes: > I am looking for something like > string_agg(distinct col_x order by col_y) > Unfortunately, you can either have the distinct, but then the order by > needs to be identical to what's aggregated, or you can have the order be > determin

Re: string_agg distinct order by

On Thu, Aug 19, 2021 at 9:09 AM Markhof, Ingolf < ingolf.mark...@de.verizon.com> wrote: > > > string_agg(distinct status,',' order by status) as list > > but this is not what I want: 'green' would get first, red second, yellow last... > > I could

string_agg distinct order by

I am looking for something like string_agg(distinct col_x order by col_y) Unfortunately, you can either have the distinct, but then the order by needs to be identical to what's aggregated, or you can have the order be determined by another column. Not both... Here is the playground

Re: order by

Luca Ferrari writes: > The ORDER BY rejects non existent columns (right) but accepts the > table itself as an ordering expression. As others have noted, this is basically taking the table name as a whole-row variable, and then sorting per the rules for composite types. I write to point ou

Re: order by

On Thu, 2021-06-10 at 10:39 +0200, Luca Ferrari wrote: > I also realized that this "table to tuples" expansion works for GROUP BY too. > However, I'm not able to find this documented in GROUP BY, WHERE, > ORDER BY clauses sections into select documentation > https://www

Re: order by

On Thu, Jun 10, 2021 at 8:40 PM Luca Ferrari wrote: > On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain > > when you provide a table in query in the order by clause, it is > > ordered by cols of that table in that order. > > Clever, thanks! > I also realized that this &qu

Re: order by

On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain wrote: > you can run an explain analyze to check what is going on, > when you provide a table in query in the order by clause, it is > ordered by cols of that table in that order. Clever, thanks! I also realized that this "t

Re: order by

> Any hint? you can run an explain analyze to check what is going on, when you provide a table in query in the order by clause, it is ordered by cols of that table in that order. create table t(id int, value int); postgres=# explain (analyze,verbose) select * from t order b

order by

Hi all, this could be trivial, but I found as an accident the following: pgbench=> create table t as select v from generate_series( 1, 2 ) v; SELECT 2 pgbench=> select * from t order by foo; ERROR: column "foo" does not exist LINE 1: select * fro

Re: Suboptimal plan when IN(...), ORDER BY and LIMIT are used (no JOINs)

, '1001361706624116300', '1001338330225145648', '1001363186688934748', '1001366841628692013' ) ORDER BY created_at DESC LIMIT 2; Limit (cost=49712.75..49712.99 rows=2 width=42) (actual time=82.611..83.462 rows=2 loops=1) -> Gather Merge (cost=49712.75..674

Re: Suboptimal plan when IN(...), ORDER BY and LIMIT are used (no JOINs)

Your query and explain analyze output do not seem to match. Filter: (cred_id = '1001344096118566254'::bigint) I don't see anything like that in your query, nor an index that would support accomplishing that without filtering after fetching the 184k rows initially like the planner does. >

  1   2   >