Not sure if I should CREATE INDEX for text columns on which I plan to filter later

Hello, in a 2 player game I store all games in the following PostgreSQL 10.2 table: CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, finished timestamptz, player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

I’m no expert but I’d think it unlikely an index would be considered for a table with only 100 rows in. Also I’m pretty sure only one index per table is used, so you’d want to put state1 and state2 in one index. You may wish to consider normalising too – so any field with a 1 or 2 at the end

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

Hi Martin - On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore wrote: > I’m no expert but I’d think it unlikely an index would be considered for a > table with only 100 rows in. Also I’m pretty sure only one index per table > is used, so you’d want to put state1 and state2 in one index. > I hope to

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

Here is the current DESC of the table (I already use few joins): words=> \d words_games Table "public.words_games" Column | Type | Collation | Nullable | Default --+--+---+-

Installing 9.6.6 to a RHEL 6.7 server with no Internet access

Hi. According to https://www.postgresql.org/download/linux/redhat/ I must first install the repository.  However, since that system doesn't have Internet access, I need to manually copy the files from my Windows laptop to the RHEL 6.7 server and then localinstall them. So, the question: what

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

My point was that your explain would be the same with indexes as without as they won’t be used. Martin. From: Alexander Farber Date: Wednesday, 21 February 2018 at 12:33 Cc: pgsql-general Subject: Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

On Wed, 21 Feb 2018 13:33:18 +0100 Alexander Farber wrote: > Hi Martin - > > On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore > wrote: > > > I’m no expert but I’d think it unlikely an index would be considered for a > > table with only 100 rows in. Also I’m pretty sure only one index per table >

Re: Installing 9.6.6 to a RHEL 6.7 server with no Internet access

Not sure if this may help you. A few months ago, I made an automated OpenSUSE installer which also had to install PostgreSQL 9.6 server using the RHEL PGDG releases. There's no YUM there, so I did it instead with RPM files directly. The RPM files I included on my autoinstall ISO are the followin

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

>However, Martin's other comment about only using a single index is > incorrect. > Postgres can use multiple indexes per query, so it's often good practace to > put indexes on every column that might ever be used in a WHERE clause. > -- > Bill Moran That's ve

Building PostgreSQL old version from source to test vulnerability CVE-2017-7546

Hello. I'm trying to build a vulnerable PostgreSQL system in a Docker container in order to be able to exploit this vulnerability . I'm testing with a C# app which simply connects using a *connstring* through *npgsql *driver (this is one

Re: Installing 9.6.6 to a RHEL 6.7 server with no Internet access

It does.  Thank you. On 02/21/2018 10:03 AM, Alvaro Aguayo Garcia-Rada wrote: Not sure if this may help you. A few months ago, I made an automated OpenSUSE installer which also had to install PostgreSQL 9.6 server using the RHEL PGDG releases. There's no YUM there, so I did it instead with RPM

initdb when data/ folder has mount points

Hi, v9.6.6 Apparently, initdb assumes that data/ is one big mount point. However, we have four mount points: /var/lib/pgsql/9.6/data/backup /var/lib/pgsql/9.6/data/base /var/lib/pgsql/9.6/data/pg_log /var/lib/pgsql/9.6/data/pg_xlog They are all empty.  How do I convince it to ignore the fact

Understanding query planner cpu usage

I'm having an perplexing issue in PG 10.1 wherein deleting a large amount of rows from a table causes query planning time to spike dramatically for a while. This happens with or without autovacuums so vacuuming isn't the issue. CPU usage during this time spikes as well. I can't determine if the qu

Re: initdb when data/ folder has mount points

On Wed, 21 Feb 2018, Ron Johnson wrote: Apparently, initdb assumes that data/ is one big mount point. However, we have four mount points: /var/lib/pgsql/9.6/data/backup /var/lib/pgsql/9.6/data/base /var/lib/pgsql/9.6/data/pg_log /var/lib/pgsql/9.6/data/pg_xlog Ron, What command do you use?

Re: initdb when data/ folder has mount points

Ron Johnson writes: > Apparently, initdb assumes that data/ is one big mount point. However, we > have four mount points: > /var/lib/pgsql/9.6/data/backup > /var/lib/pgsql/9.6/data/base > /var/lib/pgsql/9.6/data/pg_log > /var/lib/pgsql/9.6/data/pg_xlog Don't do that. There's no reason for backu

Re: Understanding query planner cpu usage

Lucas Fairchild-Madar writes: > I'm having an perplexing issue in PG 10.1 wherein deleting a large amount > of rows from a table causes query planning time to spike dramatically for a > while. This happens with or without autovacuums so vacuuming isn't the > issue. Would the deleted rows happen t

Re: initdb when data/ folder has mount points

On 02/21/2018 06:01 PM, Tom Lane wrote: Ron Johnson writes: Apparently, initdb assumes that data/ is one big mount point. However, we have four mount points: /var/lib/pgsql/9.6/data/backup /var/lib/pgsql/9.6/data/base /var/lib/pgsql/9.6/data/pg_log /var/lib/pgsql/9.6/data/pg_xlog Don't do that

Re: initdb when data/ folder has mount points

Ron Johnson writes: > On 02/21/2018 06:01 PM, Tom Lane wrote: >> Ron Johnson writes: >>> Apparently, initdb assumes that data/ is one big mount point. However, we >>> have four mount points: >>> /var/lib/pgsql/9.6/data/backup >>> /var/lib/pgsql/9.6/data/base >>> /var/lib/pgsql/9.6/data/pg_log >>>

Re: Understanding query planner cpu usage

On Wed, Feb 21, 2018 at 4:14 PM, Tom Lane wrote: > Lucas Fairchild-Madar writes: > > I'm having an perplexing issue in PG 10.1 wherein deleting a large amount > > of rows from a table causes query planning time to spike dramatically > for a > > while. This happens with or without autovacuums so

Re: initdb when data/ folder has mount points

On 2/21/18 7:01 PM, Tom Lane wrote: Ron Johnson writes: Apparently, initdb assumes that data/ is one big mount point. However, we have four mount points: /var/lib/pgsql/9.6/data/backup /var/lib/pgsql/9.6/data/base /var/lib/pgsql/9.6/data/pg_log /var/lib/pgsql/9.6/data/pg_xlog Don't do that.

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

On 02/21/2018 01:33 PM, Alexander Farber wrote: > Hi Martin - > > On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore > wrote: > > I’m no expert but I’d think it unlikely an index would be considered > for a table with only 100 rows in. Also I’m pretty sure onl

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

On 02/21/2018 05:00 PM, Bill Moran wrote: > On Wed, 21 Feb 2018 13:33:18 +0100 > Alexander Farber wrote: > >> Hi Martin - >> >> On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore >> wrote: >> >>> I’m no expert but I’d think it unlikely an index would be considered for a >>> table with only 100 rows

PostgreSQL backup stategies

Dear Friends, Please, somebody knows a good source of information about PostgreSQL 9 backup strategies for production systems, if you have read a good book with this subject, could you share the author and name of the book ? Thanks

Re: PostgreSQL backup stategies

On Wed, Feb 21, 2018 at 10:00 PM, Luis Marin wrote: > Dear Friends, > > Please, somebody knows a good source of information about PostgreSQL 9 > backup strategies for production systems, if you have read a good book with > this subject, could you share the author and name of the book ? > > Thanks

Re: Understanding query planner cpu usage

Lucas Fairchild-Madar writes: > On Wed, Feb 21, 2018 at 4:14 PM, Tom Lane wrote: >> If so, this might be some manifestation of a problem we've seen before: >> the planner tries to find out the current live max value of the column >> by scanning the index, and that's really slow if there are a lot

oracle to postgresql conversion tool

Hi, there are at least 5 tools I found on the PG list but could you recommend well tested, free one ? we need to migrate production 30GB oracle 11 db to postgres 9 and are looking for best approach. Of course if there is no free/open solution any commercial & recommended tool is always welcome.

Re: oracle to postgresql conversion tool

On Thu, Feb 22, 2018 at 3:59 PM, Marcin Giedz wrote: > Hi, there are at least 5 tools I found on the PG list but could you > recommend well tested, free one ? we need to migrate production 30GB oracle > 11 db to postgres 9 and are looking for best approach. Of course if there > is no free/open so

Re: oracle to postgresql conversion tool

Hi 2018-02-22 5:59 GMT+01:00 Marcin Giedz : > Hi, there are at least 5 tools I found on the PG list but could you > recommend well tested, free one ? we need to migrate production 30GB oracle > 11 db to postgres 9 and are looking for best approach. Of course if there > is no free/open solution an

Re: oracle to postgresql conversion tool

On Feb 22, 2018 11:16 AM, "Pavel Stehule" wrote: Hi 2018-02-22 5:59 GMT+01:00 Marcin Giedz : > Hi, there are at least 5 tools I found on the PG list but could you > recommend well tested, free one ? we need to migrate production 30GB oracle > 11 db to postgres 9 and are looking for best approac

Re: initdb when data/ folder has mount points

On Wed, Feb 21, 2018 at 07:56:38PM -0500, David Steele wrote: > On 2/21/18 7:01 PM, Tom Lane wrote: >> For pg_log, just put it somewhere else and set the appropriate >> configuration option to say where to write the postmaster log files. >> Or you could use a symlink, like the solution for pg_xlog,