On 7 July 2015 at 10:52, Daniele Varrazzo
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.2ndquadrant.co
INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id WHERE a.id >= 800;
Of course, you'd need to be very careful to ensure that the results of each
SELECT never overlap. It would be nice to invent some better way than this
that divided the workload evenly even when the tables grow.
Then yo
in memory?
>
>
It depends which memory you're talking about. If you mean pages that are in
the shared buffers then you can just
EXPLAIN (ANALYZE, BUFFERS) select ... from table;
You'll see Buffers: shared read=N if any buffers were "read from disk" but
kee
ttname | null_frac
-+---
a | 0.33
b | 0.67
c | 1
Keep in mind though that this is an *approximation* and possibly could be
inaccurate. If you want exact do the COUNT(col).
If you're not on a version new enough for COUNT(c
ome outer 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.2ndqua
our 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
ime=159.840..201915.765
> rows=7672 loops=1)
> Filter: (group_id = 45)
> Rows Removed by Filter: 212699113
>
>
Rows Removed by Filter: 212699113 seems to indicate that your 473733 row
count for "customers" is incorrect.
If you're doing lots of filtering on
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 (pgs
-11 17:23:40
>> 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
atic/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
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.
-> 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 D
On 1 October 2016 at 10:10, Tom Lane wrote:
> David Rowley writes:
>> On 1 October 2016 at 05:47, Tom Lane wrote:
>>> Somebody will need to trace through this on Windows and see where it's
>>> going off the rails.
>
>> I tried the test case on 9.6
ing.
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 slowly when you run it for a 2n
s, 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 subscription:
http://www.postgresql.org/mailpref/pgsql-general
x27;url','industry','status','comment')
> VALUES
> (1,'AG Spray Inc.',' ','PO Box
> 12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937',
er with a common table expression
such as:
with cte (select owner_id from settings 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
e 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.
--
Dav
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 executed.
--
David Row
_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
;
> Would anyone here point me in 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'&file=\\1');
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
iver,recipient from
(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
t, 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
On 30 December 2015 at 13:56, Hiroyuki Sato wrote:
> 2015年12月30日(水) 6:04 David Rowley :
>
>> On 30 December 2015 at 04:21, Hiroyuki Sato wrote:
>>
>>> 2015年12月29日(火) 4:35 Jeff Janes :
>>>
>>>>
>>>>
>>> But, the planner refus
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
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.2ndquadrant.com/>
P
d to
> be raised.
>
> http://www.postgresql.org/docs/9.1/interactive/sql-droptrigger.html
>
> http://www.postgresql.org/docs/9.4/interactive/sql-droptrigger.html
Seems to have been changed in
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b152c6cd0de1827
On 14 January 2016 at 12:08, Adrian Klaver
wrote:
> On 01/13/2016 02:51 PM, David Rowley wrote:
>
>> On 14 January 2016 at 11:32, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 01/13/2016 02:24 PM, Tom Lane wrote
f 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
On 15 January 2016 at 04:00, Tom Lane wrote:
> David Rowley writes:
> > Perhaps separating out enable_nestloop so that it only disables
> > non-parameterised nested loops, and add another GUC for parameterised
> > nested loops would be a good thing to do. Likely setting
ing 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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 pgsql-general mailing li
up in the 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
ill 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 query 1 significantly.
--
Da
eddecimal 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 Development, 24x7
TEs 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 Development, 24x7 Support
r 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 Rowley
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 unique.
Is there a special reas
odifying 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
parameter in t
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 in the schemas or security,
>
ds... probably not better. You can test this yourself
with; SET enable_nestloop = 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 mi
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 th
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 http://www.2ndQuadrant.com/
Postg
greSQL (pre
8.4) you may 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
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 pgsql-general mailing list (pgsql-gener
there are 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
enefit 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.
--
David Rowley
psql, you can execute the
above then execute \gexec which will execute 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.postgresql.org/mailpref/pgsql-general
table_constraints row 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
erencing 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.postgresql.org/mailpref/pgsql-general
#x27;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 & Serv
this 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 &a
.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@postgresql.org)
To m
]
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, Training &a
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 Development
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
On 10 October 2017 at 02:51, Tom Lane wrote:
> David Rowley 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...
>
> I think it's actually disallowed by the SQL spec (al
On 10 October 2017 at 12:44, Tom Lane wrote:
> David Rowley 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
>> couldn't we make is_simple_subquery() a bit smarter a
he 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/
PostgreSQL Development, 24
that the planner was able to pull the
subquery (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
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 anyon
Mircosoft's native client
rather than tds. 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:
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.
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.
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
> 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 stoc
> -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 tr
> -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
Madison Kelly Wrote:
> David Fetter wrote:
> > On Mon, Dec 08, 2008 at 11:16:29PM -0000, David Rowley wrote:
> >>> -Original Message-
> >>> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> >>> [EMAIL PROTECTED] On Behalf Of Madison Kelly
>
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
2008/12/14 Dmitry Koterov :
> 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 "id
2008/12/14 Dmitry Koterov :
>
> On Sun, Dec 14, 2008 at 3:36 PM, David Rowley wrote:
>>
>> 2008/12/14 Dmitry Koterov :
>> > The question: if the table "tbl" scanned to fetch "id" and calculate
>> > md5(id), or the value of "id"
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.
--
73 matches
Mail list logo
