Re: [GENERAL] Equivalence Classes when using IN

uery (or view) up a level. When the planner is able to do this it's much more flexible to the types of plans it can generate. It's just that we don't ever pull up subqueries with DISTINCT ON, plus a bunch of other reasons. -- David Rowley http://www.2ndQuadrant.com/

Re: [GENERAL] Equivalence Classes when using IN

gs If you > allow the WHERE to pass through ORDER BY. > > A special case can be allowed for WHERE to pass the ORDER BY if the column is > part of DISTINCT ON. Yeah, we do allow predicates to be pushed down in that case. -- David Rowley http://www.2ndQuadrant.com/

Re: [GENERAL] Equivalence Classes when using IN

On 10 October 2017 at 12:44, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> If the only reason that is_simple_subquery() rejects subqueries with >> ORDER BY is due to wanting to keep the order by of a view, then >> co

Re: [GENERAL] Equivalence Classes when using IN

On 10 October 2017 at 02:51, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> It's pretty bad practice to have ORDER BY in views. I kinda wish we >> didn't even allow it, but that ship sailed many years ago... > >

Re: [GENERAL] Equivalence Classes when using IN

mer_id) to parameterise the nested loop, at least, it likely would, if you have one. It's pretty bad practice to have ORDER BY in views. I kinda wish we didn't even allow it, but that ship sailed many years ago... -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Deve

Re: [GENERAL] OR-clause support for indexes

is being worked on? Any progress in btree-support? Not exactly what you're asking, but perhaps https://commitfest.postgresql.org/14/1001/ could improve your workload, or perhaps you could just manually rewrite the query. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL De

Re: [GENERAL] Equivalence Classes when using IN

es. [1] https://www.postgresql.org/message-id/flat/CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A%40mail.gmail.com#cakjs1f9fk_x_5hkcpcseimy16owe3empmmgsgwlckkj_rw9...@mail.gmail.com -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Trai

Re: [GENERAL] a JOIN to a VIEW seems slow

ow_id GROUP BY c.due_row_id ) c ON c.due_row_id = a.row_id; SQL Server will probably be doing this rewrite. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@pos

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

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: [GENERAL] Select from tableA - if not exists then tableB

e from (select *,'a' tablename from a where id=1 union all select *,'b' tablename from b where id=1) ab order by id,tablename; Assuming that id is what you want to be unique. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &

Re: [GENERAL]

g or referenced in a foreign key constraint. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.post

Re: [GENERAL]

matching the join condition. If you can state what you want to achieve then I'm sure someone will help. (Please, in the future, give your emails a suitable subject line) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Serv

Re: [GENERAL] all serial type was changed to 1

ecute the previous result set as commands. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgre

Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

ere is no planning benefit to setting up > two deferrable references constraints to enforce the non-optional > requirement? There is no place in the planner where a foreign key is used as a proof that a joined row must exist, with the exception of row estimations for statistics. -- Dav

Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

windows where they may not actually hold true to their word. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why so long?

added pages are more likely to be cached. You may also want to consider running the EXPLAIN (ANALYZE, BUFFERS) after having SET track_io_timing = on; -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgs

Re: [GENERAL] DISTINCT vs GROUP BY - was Re: is (not) distinct from

ay have also preferred to use GROUP BY over DISTINCT as GROUP BY could be implemented internally by sorting or hashing the results, whereas DISTINCT used to only be implemented by Sorting the results. Although this has long since been the case. -- David Rowley http://www.2ndQuadrant

Re: [GENERAL] 9.6.1: INSERT with PK as serial

','PO Box > 12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937','i...@agsprayinc.com','','Chemicals','Opportunity',''), single quotes are strings. double quotes may be used for identifiers. In the column names list here you've mixed up the two. -- David Rowley

Re: [GENERAL] 9.6.1: INSERT with PK as serial

number of values, in the same order as the columns defined on the table. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscrip

Re: [GENERAL] Slow index scan - Pgsql 9.2

, BUFFERS) might reveal something. Perhaps each of the 2513 found rows, plus the 1068 filtered out rows were spread over the table. Perhaps each on their own heap page, and all those pages had to be read from disk. The BUFFERS option might help show if this is the case. Does it execute as

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

On 1 October 2016 at 10:10, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> On 1 October 2016 at 05:47, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> Somebody will need to trace through this on Windows and see where

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

> Parallel Seq Scan on big (cost=0.00..222744.43 rows=3 width=4) (actual time=0.143..992.890 rows=294118 loops=6) Filter: ((id % 17) = 0) Rows Removed by Filter: 4705883 -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Dev

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

0 That's interesting. How about trying to increase max_worker_processes to say, 16. I know you've said you've tried multiple times and it seems consistent, but increasing this seems to be worth a try, if anything, to rule that out. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

/static/auto-explain.html) Setting this up will log the EXPLAIN ANALYZE to the PostgreSQL logs when you execute the query as normal. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing lis

Re: [GENERAL] select date between - PostgreSQL 9.5

gt; 2016-09-13 20:00:51 >> 2016-09-13 20:00:51 >> 2016-09-13 20:00:51 >> 2016-09-13 20:00:51 > > > > What am I doing wrong? None of those dates are between your specified date range. If you want to include all of 2016-09-13 timestamps, then you'd better do <

Re: [GENERAL] Question about antijoin

where not exists(select * from b where a.id=b.id and a.id > random()); See: convert_EXISTS_sublink_to_join() for details. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list

Re: [GENERAL] Slow query when the select list is big

e single highest j1031101.validfrom row and performing the joins to the other table on that single row, but that will depend on which windowing function you're using as the function may require the other rows in the window frame to calculate the correct result. -- David Rowley htt

Re: [GENERAL] Slow join over three tables

f it ... or > could further tweaking to PostgreSQL's configuration be of any help here? EXPLAIN ANALYZE also has quite a bit of timing overhead, so it might not be taking quite as long as you think. How long does it take with EXPLAIN (ANALYZE, TIMING OFF) ... ? Or perhaps just run the que

Re: [GENERAL] Slow join over three tables

op = 0; you might need to SET enable_mergejoin = 0; too. I imagine it'll be slower. Likely the only way to speed this up would be to create indexes; create index on reports (id, age, gender, created); the above might allow an index only scan, which should speed up the nested loop a bit. This will

Re: [GENERAL] Enhancement request for pg_dump

On 18 April 2016 at 13:10, Sergei Agalakov wrote: > Thank you, I know this place. > I just wanted to check that my request will have the peoples support. > So far it doesn't. It looks like that or people never need to compare two PG > databases to find the differences

Re: [GENERAL] Horrible/never returning performance using stable function on WHERE clause

ow, just by modifying your query to become: select max(rh) into tmp from items where vslwhid=vvslid and itoar(defid) ~ (select get_machdef_sister_defids(vdefid)); Viewing the EXPLAIN of this, you'll notice the InitPlan, which will evaluate the function and allow the use the output value as a paramete

Re: [GENERAL] BRIN Usage

On 18/02/2016 9:34 am, "Tom Smith" wrote: > > Hi: > > I feel it is a stupid question. > > Can BRIN index enforce uniqueness? > My issue is > the column I'd like to apply BRIN index also needs to be unique > (think of timestamp as primary key). Only btree supports

Re: [GENERAL] comparison between Postgresql and Microsoft SQL Server

at will be a substantial shortcut for me. Well that's a pretty big topic, and you've not hinted much as to which aspects you'd like to compare. Perhaps somewhere like [1] might be a good start. [1] https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems -- David Rowle

Re: [GENERAL] BRIN indexes

e order. Although UPDATEs might create new tuples in some free space elsewhere in the relation, but it's not hard to imagine other cases where there's no updates and "natural correlation" is persisted. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Develop

Re: [GENERAL] Performance options for CPU bound multi-SUM query

provements from the fixeddecimal type than you won't have already gotten from float8. My tests showed that it's very slightly slower than float8, which is possibly due to float8 addition not having overflow checks. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Developmen

Re: [GENERAL] Performance options for CPU bound multi-SUM query

an option and it will offer much faster aggregation. There is also https://github.com/2ndQuadrant/fixeddecimal which may be of some use if you need fixed precision up to a predefined scale. We found that using fixeddecimal instead of numeric for the TPC-H benchmark improved performance of qu

Re: [GENERAL] 9.5 new features

during a scan, and also maintains sequential read speeds which I don't think would work quite as efficiently with btree index performing heap lookups. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsq

Re: [GENERAL] repeated characters in SQL

he regular expression, then the value matching the . gets stored in the variable \1, so your regex basically says; "match a single character which has the same single character to its immediate right hand side". The extra \ is just an escape character. -- David Rowley

Re: [GENERAL] 9.5 new features

nge") during execution time. So I agree with the "automatic partitioning" description. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make change

Re: [GENERAL] Why PG uses nested-loop join when no indexes are available?

to off in production would be a slightly easier decision to make, if that was the case. It looks pretty simple to do this, so I hacked it up, and attached it here. There's no doc changes and I'm not that interested in fighting for this change, it's more just an idea for consideration. -- David Rowley

Re: [GENERAL] Why PG uses nested-loop join when no indexes are available?

On 15 January 2016 at 04:00, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: > > Perhaps separating out enable_nestloop so that it only disables > > non-parameterised nested loops, and add another GUC for parameterised >

Re: [GENERAL] v9.1, DROP TRIGGER IF EXISTS behaving oddly

esql.git;a=commit;h=b152c6cd0de1827ba58756e24e18110cf902182a Perhaps that commit should have also made changes to the documents to change things such as: Do not throw an error if the trigger does not exist. A notice is issued in this case. To Do not throw an error if the trigger or table does not exist. A notice is issued in this case. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] v9.1, DROP TRIGGER IF EXISTS behaving oddly

On 14 January 2016 at 12:08, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 01/13/2016 02:51 PM, David Rowley wrote: > >> On 14 January 2016 at 11:32, Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@aklaver.com>> wrote: >> >

Re: [GENERAL] Code of Conduct: Is it time?

courage more people to do as I did, then I think that would be a bonus. Perhaps someone may mumble something in disagreement about that though. It's hard for me to imagine that I've been the only person to do this. -- David Rowley http://www.2ndQuadrant.com/ <http://www.

Re: [GENERAL] Efficiently selecting single row from a select with window functions row_number, lag and lead

id, (select max(id) from route where id < 1350) as prev, (select min(id) from route where id > 1350) as next from route where id=2; ? That should be much more efficient for a larger table as it should avoid the seqscan and allow the index to be used for all 3 numbers. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] grep -f keyword data query

On 30 December 2015 at 13:56, Hiroyuki Sato <hiroys...@gmail.com> wrote: > 2015年12月30日(水) 6:04 David Rowley <david.row...@2ndquadrant.com>: > >> On 30 December 2015 at 04:21, Hiroyuki Sato <hiroys...@gmail.com> wrote: >> >>> 2015年12

Re: [GENERAL] grep -f keyword data query

plits that out for you and returns it, then index that function, and then just include a call to that function in the join condition matching with the equality operator. That'll allow hash and merge joins to be possible again. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] Secret Santa List

rom (select row_number() over (order by random()) rn, giver from secretsanta) g inner join (select row_number() over (order by random()) rn, giver recipient from secretsanta) r on g.rn = r.rn ) update secretsanta set recipient = cte.recipient from cte WHERE cte.giver = secretsanta.giver; -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] regexp_replace question / help needed

the right direction? > I think you're just missing the capture group to grab the filename. What you capture in the group is then available to use as you please in \1 (which needs be escaped as \\1) so something like regexp_replace(' http://test.com/test/testfile.php','/([^/]*$)', E'=\\1'); -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] aggregation question

n either > the home_club or away_club columns. > > How would I do that? > Use UNION: select home_club from fixtures UNION select away_club from fixtures; -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] Re: [GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

r perhaps a LIMIT or OFFSET. You can view the output from the planner by prefixing your SELECT statement with EXPLAIN: See http://www.postgresql.org/docs/current/static/sql-explain.html This will give you the information you need to see how the query planner has decided on how your query will be

Re: [GENERAL] Importing directly from BCP files

g along the lines of: copy from '' delimiter ' '; -- <- use a tab character between the quotes. Of course, you'll need to create the table first with CREATE TABLE. > Has anybody done anything like this before? > > I'd imagine that it's fairly common. -- David Rowley

Re: [GENERAL] Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

s where setting_id = 1 and setting_value = 'common_1') as select id1 from multi_id where id1 in (select owner_id from cte) union select id2 from multi_id where id2 in (select owner_id from cte) union select id3 from multi_id where id3 in (select owner_id from cte); but you still have the union overhead. -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] Merge join vs merge semi join against primary key

of filtering on group_id, then perhaps you should think about adding an index on customers (group_id,id) -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] How to speed up delete where not in

ked up your tables locally, and populated them with the same number of records as your row estimates in the EXPLAIN you pasted and I got: test=# delete from omdok where not exists (select 1 from omrid where omdok.dokumn = omrid.dokumnr); DELETE 0 Time: 1698.233 ms Whereas with the NOT IN() I cancelled it after 10 minutes. Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] Automatically Updatable Foreign Key Views

ter query. If foreign keys were updated immediately, like indexes normally are, then this wouldn't be an issue. I've attached a file with 2 examples of when this can happen. Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/&

Re: [GENERAL] Count of non-null values per table column

IS NULL THEN 1 ELSE 0 END) Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Training Services

Re: [GENERAL] Postgresql jsonb

EXPLAIN (ANALYZE, BUFFERS) select ... from table; You'll see Buffers: shared read=N if any buffers were read from disk but keep in mind they still might not be coming from disk, they could be cached by the operating system in memory. Regards David Rowley -- David Rowley http://www

Re: [GENERAL] Creating table with data from a join

some better way than this that divided the workload evenly even when the tables grow. Then you could run these concurrently. Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Training Services

Re: [GENERAL] Missing space in message

On 7 July 2015 at 10:52, Daniele Varrazzo daniele.varra...@gmail.com wrote: Patch attached. Thanks for the patch. Would you be able to post it to pgsql-hack...@postgresql.org instead? Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ http://www

Re: [GENERAL] keeping track of function execution

Wojtek wrote: But... Postgress treats function as single transaction, of course. Hence, I'm not able to see any changes in my progress monitoring table until my main function is finished and all the statuses are set to 0. You could use dblink() to insert into your logging table. David. --

Re: [GENERAL] Is index enough to do simple fetch, or table is always used too?

2008/12/14 Dmitry Koterov dmi...@koterov.ru: Hello. Suppose I have the following index: CREATE INDEX idx ON tbl USING btree (abc, def, id) and perform the query with index scan: SELECT md5(id) FROM tbl WHERE abc=1 AND def=2 LIMIT 200 The question: if the table tbl scanned to fetch

Re: [GENERAL] Is index enough to do simple fetch, or table is always used too?

2008/12/14 Dmitry Koterov dmi...@koterov.ru: On Sun, Dec 14, 2008 at 3:36 PM, David Rowley dgrow...@gmail.com wrote: 2008/12/14 Dmitry Koterov dmi...@koterov.ru: The question: if the table tbl scanned to fetch id and calculate md5(id), or the value of id is brought directly from idx index

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 at least it's standard

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, I've got a table that I am trying to

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 08, 2008 at 11:16:29PM -, David Rowley wrote

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

Madison Kelly Wrote: 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

Re: [GENERAL] Query too slow with not in condition

I have loaded the backup from a live database in a test system. Both run 8.3.5 versions. The plan for a query varies in these systems. Test System A. PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (SUSE Linux) B. explain select * from

Re: [GENERAL] Numbering rows

Andreas Kretschmer wrote: Can you show an example for 8.4? It's not 100% certain that it will be possible for 8.4, probably though. select row_number() over (order by employeeid) as nrow,* from employee order by employeeid It's important to have both the order bys There is more information on

Re: [GENERAL] Sample databases

Tommy Gibbons wrote: I would like some pointers as to how to install the dbsamples so that I can use them in Postgres. These .tar.qz files seem to contain *.sql files. These seem to be text files but I do not understand how to import to postgres or if there is some command line command to run.

Re: [GENERAL] Indexing problem with OFFSET LIMIT

I'm no expert at reading query plans, but I'm guessing the planner chose the other plan because your offset + limit went beyond the row estimate. Look's like it's then doing a disk based sort in the other plan which probably explain why it's slow. Someone please correct me if I'm wrong.

Re: [GENERAL] dblink to non postgresql dbms

. The odbclink does not have any files yet, but looks like exactly what I'd need. David. -Original Message- From: Klint Gore [mailto:[EMAIL PROTECTED] Sent: 27 June 2008 00:45 To: David Rowley Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] dblink to non postgresql dbms David Rowley

[GENERAL] dblink to non postgresql dbms

Hello all, I'm looking for a solution to query a SQL Server 2000 instance from PostgreSQL 8.3.3. I've been trawling the internet for some type of solution with out any luck, I only found old references to someone talking about implementation of create database link to postgresql Does