Hi All,
So, I have a table that looks like this:
CREATE TABLE config (
region varchar(10),
name varchar(10),
value varchar(40)
);
Another looks like this:
CREATE TABLE tag (
host varchar(10),
On 22/02/2016 13:03, Chris Withers wrote:
Hi All,
So, I have a table that looks like this:
CREATE TABLE config (
regionvarchar(10),
namevarchar(10),
valuevarchar(40)
);
Another looks like this:
CREATE TABLE tag (
hostvarchar(10),
typevarchar(10),
va
Tom, thanks for your unbiased detailed response.
Interesting post.
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane
Sent: 22 February 2016 05:06
To: Andrew Smith
Cc: pgsql-general@postgresql.org
Subject: Re: [G
All,
* FarjadFarid(ChkNet) (farjad.fa...@checknetworks.com) wrote:
> Tom, thanks for your unbiased detailed response.
>
> Interesting post.
Please don't top-post. My comments are in-line, below.
> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-o
hi,
I don't understand why the query planner is choosing a BitmapAnd when an
Index Scan followed by a filter is obviously better.
(Note that "new_york_houses" is a view of table "houses" with one
condition on city - and there is an index idx_houses_city. That is the
Index Scan that I think it sho
Greetings.
I'd like to understand why Postgres behaves the way it does.
I was not able to find relevant mail thread myself, if one exists — please,
point at it.
Test setup:
PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-16), 64-bit
create table
Victor Yegorov schrieb am 22.02.2016 um 16:45:
> Test setup:
>
> PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-16), 64-bit
>
> create table t(t_id int4, sn_c char(20));
> insert into t select id,
> chr((random()*26)::int4+65)||chr((random()*
Seamus Abshere writes:
> I don't understand why the query planner is choosing a BitmapAnd when an
> Index Scan followed by a filter is obviously better.
> 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
[ squin
2016-02-22 18:00 GMT+02:00 Thomas Kellerer :
> I assume that this has to do with the fact that char(n) is blank padded to
> 20 character.
>
Yes, this was my thought too, but I do not understand what is going on in
details.
To be able to correctly compare that to a text value, sn_c has to be cas
Tom, all,
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Seamus Abshere writes:
> > I don't understand why the query planner is choosing a BitmapAnd when an
> > Index Scan followed by a filter is obviously better.
>
> > On Postgres 9.4.4 with 244gb memory and SSDs
>
> > maintenance_work_mem 100
>
Stephen Frost writes:
> I've not looked into the specific costing here to see why the BitmapAnd
> ended up being chosen over just doing an index scan with the btree and
> then filtering, but I do believe it to be a problem area that would be
> good to try and improve. The first question is probab
On Mon, Feb 22, 2016, at 01:48 PM, Tom Lane wrote:
> Given how remarkably quick the single-index scan is, I also wonder if that
> index is fully cached while we had to read some of the other index from
> kernel or SSD.
This makes sense, except that the speed of the query is the same if I
run it
Victor Yegorov writes:
> Well, for `varchar` type Postgres is able to do `varchar` -> `bpchar` cast
> for my constant. I do not understand why for `text` it cannot and casts
> column instead.
In cross-type comparisons like these, the parser basically has a choice
between whether to apply texteq o
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost writes:
> > I've not looked into the specific costing here to see why the BitmapAnd
> > ended up being chosen over just doing an index scan with the btree and
> > then filtering, but I do believe it to be a problem area that would be
> > good
Stephen Frost writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> Given how remarkably quick the single-index scan is, I also wonder if
>> that index is fully cached while we had to read some of the other index
>> from kernel or SSD.
> Unfortunately, this doesn't actually hold water for the case
On Mon, Feb 22, 2016, at 02:14 PM, Tom Lane wrote:
> IOW, almost certainly we *don't* realize that the query will involve scanning
> through gigabytes of index pages. But btree indexes are much simpler and
> easier to make that estimate for...
Isn't this the crux of my issue, at least?
--
Se
Seamus Abshere writes:
> On Mon, Feb 22, 2016, at 02:14 PM, Tom Lane wrote:
>> IOW, almost certainly we *don't* realize that the query will involve
>> scanning through gigabytes of index pages. But btree indexes are much
>> simpler and easier to make that estimate for...
> Isn't this the crux
Seamus Abshere writes:
> Is there any other way to differentiate the 2 index scans? FWIW, 10% of
> houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to
> drop the index like Tom said.)
Hm. 10% is above the threshold where I'd usually think that an indexscan
could beat a seqsca
On Mon, Feb 22, 2016 at 8:20 AM, Stephen Frost wrote:
>
> Also agreed here, but I've seen field evidence (with reasonable
> configurations) that definitely shows that we're a bit too happy to go
> with a BitmapAnd scan across two indexes where one returns an order of
> magnitude (or less) pages to
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
--+
Jeff Janes writes:
> I looked into this before as well, and I think it is vastly
> underestimating the cost of adding a bit into the bitmap, near this
> comment:
> /*
> * Charge a small amount per retrieved tuple to reflect the costs of
> * manipulating the bitmap. This
Seamus Abshere 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?
Use a gentle tap, man, don't swing the hammer with quite so much abandon.
I'd have tried doubling the setting to start with. Raisi
On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote:
> Seamus Abshere 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?
>
> Use a gentle tap, man, don't swing the hammer with quite so much aba
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 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?
> >
>
Hi All;
we've found a post about dumping blobs:
/
/
/I wrote: [fanlijing wants to write bytea to file] > A simple > COPY
(SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) >
should do the trick. Corrections: a) "binary" must be surrounded by
single quotes. b) *that won't dum
On 02/22/2016 11:08 AM, CS DBA wrote:
Hi All;
we've found a post about dumping blobs:
/
/
/I wrote: [fanlijing wants to write bytea to file] > A simple > COPY
(SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) >
should do the trick. Corrections: a) "binary" must be surrounded
* Seamus Abshere (sea...@abshere.net) wrote:
> Is there any other way to differentiate the 2 index scans? FWIW, 10% of
> houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to
> drop the index like Tom said.)
Have to admit that I continue to be interested in this as it might
relat
On 2/22/2016 11:23 AM, Joshua D. Drake wrote:
Can someone point me in the right direction per how I would remove the
first 25 bytes and the last 2 bytes from a bytea column?
http://www.postgresql.org/docs/9.3/static/functions-binarystring.html
Substring might do it for you.
won't doing it i
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/
Can someone point me in the right direction per how I would remove the
first 25 bytes and the last 2 bytes from a bytea column?
http://www.postgresql.org/docs/9.3/static/functions-binarystring.html
Substring might do it for you.
won't doing it in SQL still result in a BYTEA result which will
> On 22 Feb 2016, at 16:58, Tom Lane wrote:
>
> (BTW, is that index really on just a boolean column? It seems
> unlikely that "phoneable" would be a sufficiently selective
> condition to justify having an index on it. I'd seriously consider
> dropping that index as another solution approach.)
Hello
I get this error message :
ERROR: cannot convert relation containing dropped columns to view
I have googled, but find only very old posts that doesn't seem to be the
same situation.
What I have done is that I have a quite big table that I added a column
to for deletion time.
Then I drope
First of all, it would be really nice if you mentioned the version of
PostgreSQL and O/S when posing questions.
That being said, that wiki is a _discussion_, and as such, a suggestion on
how it "might" be implemented.
However, at this time, there is no such option as SET READ ONLY in any
version
On Mon, Feb 22, 2016, at 06:30 PM, Melvin Davidson wrote:
> However, at this time, there is no such option as SET READ ONLY in any
> version of PostgreSQL.
I know.
I am wondering if hypothetical read-only tables would make index-only
scans more possible by avoiding the need for row visibility ch
Hi All
I have a three node cluster using streaming replication configured as
follows:
One synch node and one asynch node.
If my synch node is down the master node is hung until that node is brought
back on line.
Two questions:
1. Is this the expected action on my master? It makes sense if
On Mon, Feb 22, 2016 at 2:38 PM, Seamus Abshere wrote:
> On Mon, Feb 22, 2016, at 06:30 PM, Melvin Davidson wrote:
> > However, at this time, there is no such option as SET READ ONLY in any
> version of PostgreSQL.
>
> I know.
>
> I am wondering if hypothetical read-only tables would make index-o
Hello,
How to get the date of creation of objects in batabase?
For example: The date of creation of tables and trigger.
Thank you;
Edson
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 02/22/2016 02:00 PM, Edson F. Lidorio wrote:
Hello,
How to get the date of creation of objects in batabase?
For example: The date of creation of tables and trigger.
AFAIK that is not recorded by default. If you are using Postgres 9.3+
you could roll your own audit system, going forward, us
On Mon, Feb 22, 2016 at 3:00 PM, Edson F. Lidorio
wrote:
> Hello,
>
> How to get the date of creation of objects in batabase?
> For example: The date of creation of tables and trigger.
>
The only source of data for that question is the local filesystem. If
that is acceptable you can find examp
Nicklas =?ISO-8859-1?Q?Av=E9n?= writes:
> create table foo
> (
> id serial,
> deleted int
> );
> alter table foo drop column deleted;
> alter table foo add column deleted timestamp;
> CREATE or replace RULE del_post AS ON DELETE TO foo
> DO INSTEAD
> update foo set deleted = now()
> WHERE id = O
You may find useful a SIEM to record activity like this.
-Original Message-
From: "David G. Johnston"
Sent: 2/23/2016 0:14
To: "Edson F. Lidorio"
Cc: "pgsql-general"
Subject: Re: [GENERAL] Get the date of creation of objects in the database
On Mon, Feb 22, 2016 at 3:00 PM, Edson F.
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 conver
Tom Lane skrev
> Nicklas =?ISO-8859-1?Q?Av=E9n?= writes:
> > create table foo
> > (
> > id serial,
> > deleted int
> > );
>
> > alter table foo drop column deleted;
> > alter table foo add column deleted timestamp;
>
> > CREATE or replace RULE del_post AS ON DELETE TO foo
> > DO INST
On Mon, Feb 22, 2016 at 2:35 PM, Seamus Abshere wrote:
> 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?
Personall
On Mon, Feb 22, 2016 at 5:18 PM, Zlatko Asenov
wrote:
> You may find useful a SIEM to record activity like this.
> --
> From: David G. Johnston
> Sent: 2/23/2016 0:14
> To: Edson F. Lidorio
> Cc: pgsql-general
> Subject: Re: [GENERAL] Get the date of creation of
Seamus Abshere writes:
> Being able to tell postgres that our table is "Read Only" has imaginary
> mystical properties for me, first and foremost being able to count
> against indexes without ever hitting the disk.
>> âIf the system is working properly then a READ ONLY table in fact should
>>
On 22-02-2016 19:42, Melvin Davidson wrote:
On Mon, Feb 22, 2016 at 5:18 PM, Zlatko Asenov
mailto:zlatko.ase...@gmail.com>> wrote:
You may find useful a SIEM to record activity like this.
From: David G. Joh
On 02/22/2016 03:24 PM, Edson F. Lidorio wrote:
+ 1
Listen11similar discussionat [1]
[1]
http://pgsql-hackers.postgresql.narkive.com/TQSHWw1l/proposal-store-timestamptz-of-database-creation-on-pg-database
Where the above leads to is implementing a version control system inside
the datab
On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek wrote:
> I have a three node cluster using streaming replication configured as
> follows:
> One synch node and one asynch node.
> If my synch node is down the master node is hung until that node is brought
> back on line.
>
> Two questions:
> 1. Is t
On Tue, Feb 23, 2016 at 3:09 PM, Michael Paquier
wrote:
> On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek wrote:
>
>> I have a three node cluster using streaming replication configured as
>> follows:
>> One synch node and one asynch node.
>> If my synch node is down the master node is hung until th
Hello
I have a three node postgresql 9.4 cluster configured with one standby in
sync mode and the other in asynch mode.
If the master datbase is shutdown the pg promote happens as expected.
However on the remaining standby node the RECOVERY.CONF file is
misconfigured when issuing the command:
Re
How do I configure both standbys as ³synch² nodes?
Do I list both nodenames or ip addresesses in the postgresql.conf
synchronous_standyby_names entry?
My current entery looks like this:
synchronous_standby_names = 'keypg2,*'
John
On 2/22/16, 8:25 PM, "Thomas Munro" wrote:
>On Tue, Feb 2
On Tue, Feb 23, 2016 at 12:02 PM, John Wiencek wrote:
> How do I configure both standbys as ³synch² nodes?
>
> Do I list both nodenames or ip addresesses in the postgresql.conf
> synchronous_standyby_names entry?
>
> My current entery looks like this:
>
> synchronous_standby_names = 'keypg2,*'
Th
On Tue, Feb 23, 2016 at 10:02 AM, Ashish Chauhan wrote:
> Thanks Venkata, I am able to setup replication now. Just wondering when I
> check replication_delay and lag, I am getting negative number, any idea why?
>
>
>
> receive|replay| replication_delay | lag
>
> --+---
David,
* David G. Johnston (david.g.johns...@gmail.com) wrote:
> The only source of data for that question is the local filesystem. If
> that is acceptable you can find examples online provided to others who have
> asked this question.
What on the local filesystem would help here..? All you kno
Stephen Frost writes:
> At least on a first blush look through the threads linked from such a
> search, I'm unimpressed by the arguments against and note that there are
> quite a few arguments for.
I think you missed the worries around what dump/reload semantics would be.
> We could provide a
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost writes:
> > At least on a first blush look through the threads linked from such a
> > search, I'm unimpressed by the arguments against and note that there are
> > quite a few arguments for.
>
> I think you missed the worries around what dump/
57 matches
Mail list logo
