/CDB_JenksBins.sql
[3]
https://www.datadoghq.com/2013/08/100x-faster-postgres-performance-by-changing-1-line/
[4]
http://blog.heapanalytics.com/dont-iterate-over-a-postgres-array-with-a-loop/
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general
DETAIL: System catalog modifications are currently disallowed.
Command was: CREATE TABLE stuff_one (
the_geom public.geometry
);
Thank you!
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
On 4/4/14, 12:58 PM, Tom Lane wrote:
Seamus Abshere sea...@abshere.net writes:
Why is pg_restore trying to put stuff into the pg_catalog schema of all places?
Hm ... does myschema actually exist in the target database? [...] if myschema
doesn't exist, the creation target devolves
would like to run EXPLAIN
and do timing tests on the queries to see the impact of not having that
index available and rewrite the query to efficiently use other indexes
if necessary.
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list
/21853-general/suggestions/245202-merge-upsert-replace
[2] http://docs.mongodb.org/manual/reference/method/db.collection.update/
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
On 7/23/14 3:40 PM, Tom Lane wrote:
John R Pierce pie...@hogranch.com writes:
On 7/23/2014 10:21 AM, Seamus Abshere wrote:
Upsert is usually defined [1] in reference to a violating a unique key:
Is this theoretically preferable to just looking for a row that
matches certain criteria, updating
On 7/23/14 6:03 PM, John R Pierce wrote:
On 7/23/2014 1:45 PM, Seamus Abshere wrote:
What if we treat atomicity as optional?
atomicity is not and never will be optional in PostgreSQL.
I'm wondering what a minimal definition of upsert could be - possibly
separating concurrency handling out
On 7/23/14 6:50 PM, David G Johnston wrote:
seamusabshere wrote
On 7/23/14 6:03 PM, John R Pierce wrote:
On 7/23/2014 1:45 PM, Seamus Abshere wrote:
What if we treat atomicity as optional?
atomicity is not and never will be optional in PostgreSQL.
I'm wondering what a minimal definition
On 7/23/14 7:45 PM, John R Pierce wrote:
On 7/23/2014 3:29 PM, Seamus Abshere wrote:
My argument lives and dies on the assumption that UPSERT would be
useful even if it was (when given with no options) just a macro for
UPDATE db SET b = data WHERE a = key;
IF NOT found THEN
INSERT
That seems like the wrong error - shouldn't it be the equiv of [...]
json and string cannot be matched?
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
hi,
I've got use cases like
array_remove(array_agg((a-'b')::float), NULL)
It would be nice to replace (a-'b')::float with something like
a-^'b'
that directly returned a numeric... is that in the cards?
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent
regexp_matches('2033 20th
Ave S', '\m(\d+(?:st|th))\M'));
?column?
--
f
(1 row)
Is there a more elegant way to compare the results of
`regexp_matches()`?
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list
On Wed, Jan 6, 2016, at 08:41 PM, Tom Lane wrote:
> Seamus Abshere <sea...@abshere.net> writes:
> > -> Can a function like `LEFT()` use an index?
> Since the question makes little sense as stated, I'm going to assume
> you mean "can a query like SELECT ... WHERE left(f
xes?)
Thanks!
Seamus
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
hi,
https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility
of `ALTER TABLE table SET READ ONLY`.
Would this mean that row visibility checks could be skipped and thus
index-only scans much more common?
Thanks,
Seamus
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com
smallint, anyarray, integer,
> internal, internal, internal, internal),
> STORAGE uuid;
Is there a reason not to put this into postgres itself? This already
exists for text[].
Thanks,
Seamus
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mai
: (phoneable = true)
I think this is why we originally set random_page_cost so "low"... it
was our way of "forcing" more index usage (we have a big, wide table).
Is there any other way to differentiate the 2 index scans? FWIW, 10% of
houses are phoneable, 0.2% are in the city. (M
On Mon, Feb 22, 2016, at 06:48 PM, David G. Johnston wrote:
> it would probably be more constructive to actually communicate the thoughts
> that provoked the question.
My company has a largish table - 250+ columns, 1 row for every household
in the US. It's read-only. We've gotten advice to
bility checks.
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
at least?
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Feb 22, 2016, at 02:30 PM, Jeff Janes wrote:
> It charges 0.1 CPU_operator_cost, while reality seemed to be more like 6
> CPU_operator_cost.
fdy=> select name, setting, boot_val from pg_settings where name ~
'cpu';
name | setting | boot_val
On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote:
> Seamus Abshere <sea...@abshere.net> writes:
> > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It
> > "fixed" my problem by preventing the BitmapAnd.
> > Is this dangerous?
>
> Us
On Mon, Feb 22, 2016, at 02:53 PM, Seamus Abshere wrote:
> On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote:
> > Seamus Abshere <sea...@abshere.net> writes:
> > > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It
> > > "fixed&q
Execution time: 4565.067 ms
> (13 rows)
On Postgres 9.4.4 with 244gb memory and SSDs
maintenance_work_mem 100
work_mem 50
random_page_cost 1
seq_page_cost 2
The "houses" table has been analyzed recently and has statistics set to
the max.
Thanks,
Seamus
--
Sea
s
Is there any way to "update *" from a record?
Thanks!
Seamus
PS. Whether I **should** do this is another matter, I just want to know
if it's possible.
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
http://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing lis
referenced if it was for a SELECT, but
since it's an UPDATE, it will be run anyway)
Thanks!
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
think the race condition would go
away.
Did I analyze that right?
Thanks!
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
Seamus Abshere <sea...@abshere.net> writes:
> That aggregates into an array. Our `jsonb_collect` aggregates into an object.
Postgres 9.6 has (per
https://www.postgresql.org/docs/9.6/static/functions-aggregate.html):
* jsonb_agg(expression)
* jsonb_object_agg(name, value)
In retrospect
,
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
hi,
# select '{"a":1}'::jsonb || null;
?column?
--
null
(1 row)
Is there a theoretical reason that this has to return null as opposed to
just {"a":1}?
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
ht
> Seamus Abshere <sea...@abshere.net> writes:
> > We do this in our database:
>
> > CREATE AGGREGATE jsonb_collect(jsonb) (
> > SFUNC = 'jsonb_concat',
> > STYPE = jsonb,
> > INITCOND = '{}'
> > );
>
> > Is there some other
. Here is the real query analyze:
https://gist.github.com/seamusabshere/b9d72132361fa598f7a431fa1bcb120f
--
Seamus Abshere, SCEA
http://faraday.io
https://github.com/seamusabshere
http://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
> On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere <sea...@abshere.net>
> > UPDATE [...] WHERE id BETWEEN 'ff00----' AND
> > 'ff0f----'
> > and
> > UPDATE [...] WHERE id BETWEEN 'f8c0----000
On 2017-10-14 16:32:33 Tom Lane wrote:
> More likely explanations for the OP's problem involve foreign key
> constraints that cause two different row updates to need to lock
> the same referenced row, or maybe he's using some index type that
> has greater locking demands than a btree, or he's
> Seamus Abshere <sea...@abshere.net> writes:
> > * I do have some exotic indexes [2]. gist, gin, postgis, fillfactor...
> On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote:
> I'd bet on the last one, especially since you found that the problem
> was a page-level lock. D
> > On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote:
> > I'd bet on the last one, especially since you found that the problem
> > was a page-level lock. Did you look to see which relation the page
> > lock was in?
On Mon, Oct 16, 2017, at 12:34 PM, Seamus Abshere wrote:
> > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote:
> >> Theoretically / blue sky, could there be a table or column type that
> >> transparently handles "shared strings" like this, reducing size on disk
> >> at the cost of lookup overhead for all q
mn type that
transparently handles "shared strings" like this, reducing size on disk
at the cost of lookup overhead for all queries?
(I guess maybe it's like TOAST, but content-hashed and de-duped and not
only for large objects?)
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://www.faraday
> On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote
> > Theoretically / blue sky, could there be a table or column type that
> > transparently handles "shared strings" like this, reducing size on disk
> > at the cost of lookup overhead for all queries?
>
On Fri, Oct 13, 2017, at 03:16 PM, David G. Johnston wrote:
> implement a "system-managed-enum" type with many of the same properties
[...]
> TOAST does involved compression but the input to
> the compression algorithm is a single cell (row and column) in a table.
> As noted above I consider the
-'
Yet one blocks the other one. How is this possible?
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
ow-to-do-histograms-in-postgresql/ which gives
plpsql so you can do:
SELECT * FROM histogram($table_name_or_subquery, $column_name)
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql
300 | 100 | 200 | 0.66
Thanks!
Seamus
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailp
lp to "redundantly" add the id ranges everywhere? (e.g, in
the where clauses of old_data AND the final update)?
Thanks!
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general
hi,
I've had an `INSERT INTO x SELECT FROM [...]` query running for more
then 2 days.
Is there a way to see how big x has gotten? Even a very rough estimate
(off by a gigabyte) would be fine.
Best,
Seamus
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https
n-postgres/
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
46 matches
Mail list logo
