Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

On Wed, May 11, 2016 at 5:30 PM, Lucas Possamai wrote: > Hi there! > > I've got a simple but slow query: > >> SELECT DISTINCT title >> FROM ja_jobs WHERE title ILIKE '%RYAN WER%' >> and clientid = 31239 AND time_job > 1457826264 >> order BY title >> limit 10 > > CREATE

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

On Wed, May 11, 2016 at 10:03 PM, Lucas Possamai wrote: > >>> >> Trying redoing the query with CTE as below: >> >> WITH ja_jobs as >> (SELECT DISTINCT title >> FROM ja_jobs >> WHERE clientid = 31239 AND time_job > 1457826264 >> ) >> SELECT title >> FROM

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

> > >> > Trying redoing the query with CTE as below: > > WITH ja_jobs as > (SELECT DISTINCT title > FROM ja_jobs > WHERE clientid = 31239 AND time_job > 1457826264 > ) > SELECT title > FROM ja_jobs > WHERE title ILIKE 'RYAN WER%' > ORDER BY title > LIMIT 10; > hmm.. still slow

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

On Wed, May 11, 2016 at 9:36 PM, Lucas Possamai wrote: > >>> >> The main problem is WHERE title ILIKE '%RYAN WER%' >> When you put a % on the left of the text, there is no way to optimize >> that, so yes, it will be slow. >> >> If you can eliminate the leading percent and

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

> > >> > The main problem is WHERE title ILIKE '%RYAN WER%' > When you put a % on the left of the text, there is no way to optimize > that, so yes, it will be slow. > > If you can eliminate the leading percent and just have trailing, it will > be much faster. > > Hmm.. yep.. I suppose I can do

Re: [GENERAL] Scaling Database for heavy load

On Wed, May 11, 2016 at 8:52 PM, Scott Marlowe wrote: > On Wed, May 11, 2016 at 4:09 AM, Digit Penguin > wrote: > > Hello, > > > > > > we use PostgreSql 9.x in conjunction with BIND/DNS for some Companies > with > > about 1.000 queries per

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

On Wed, May 11, 2016 at 8:30 PM, Lucas Possamai wrote: > Hi there! > > I've got a simple but slow query: > > SELECT DISTINCT title >> FROM ja_jobs WHERE title ILIKE '%RYAN WER%' >> and clientid = 31239 AND time_job > 1457826264 >> order BY title >> limit 10 > > > Explain

Re: [GENERAL] Scaling Database for heavy load

On Wed, May 11, 2016 at 4:09 AM, Digit Penguin wrote: > Hello, > > > we use PostgreSql 9.x in conjunction with BIND/DNS for some Companies with > about 1.000 queries per second. > Now we have to scale the system up to 100.000 queries per second (about). > > Bind/DNS is

[GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

Hi there! I've got a simple but slow query: SELECT DISTINCT title > FROM ja_jobs WHERE title ILIKE '%RYAN WER%' > and clientid = 31239 AND time_job > 1457826264 > order BY title > limit 10 Explain analyze: Limit (cost=5946.40..5946.41 rows=1 width=19) (actual > time=2746.759..2746.772

[GENERAL] Meetup in Boston city?

Boston folks: I'll be in Boston on the 24th/25th for ContinerDays. It would be nice ot meet up with other Postgres folks there ... except that it looks like the Boston PUG is located in Bedford? Is there a meetup in Boston or Cambridge? -- -- Josh Berkus Red Hat OSAS (any opinions are my own)

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, May 09, 2016 at 22:43:53 -0400, "D'Arcy J.M. Cain" wrote: Of course PHP scripts have to run as nobody so I have no choice other than to have them store passwords in various config.php files but PHP users are used to that. I would like to fix that but that's a war for

Re: [GENERAL] Invalid data read from synchronously replicated hot standby

On Wed, May 11, 2016 at 5:44 AM, wrote: >> We are getting invalid data when reading from a synchronously >> replicated hot standby node in a 2-node setup. To better understand >> the situation, we have created a document that provides an overview. >> We

Re: [GENERAL] Lightest way of checking if postgresql is running at the other end of an ssh tunnel?

On Wed, May 11, 2016 at 02:28:47PM +0200, Vik Fearing wrote: > >> We have an ssh connection running from one server to our > >> postgresql database on another server. Some times we > >> experience that the ssh tunnel does not work anymore and > >> needs to be restarted, even though we use the

Re: [GENERAL] Lightest way of checking if postgresql is running at the other end of an ssh tunnel?

On 05/11/2016 11:41 AM, Karsten Hilbert wrote: > On Wed, May 11, 2016 at 11:17:54AM +0200, Niels Kristian Schjødt wrote: > >> We have an ssh connection running from one server to our >> postgresql database on another server. Some times we >> experience that the ssh tunnel does not work anymore

Re: [GENERAL] Streaming replication, master recycling

On Wed, May 11, 2016 at 4:35 PM wrote: > I apologise for the missing data. > > we are running 9.1.15 on debian servers. > > I think there was a patch in v9.3 which makes sure that if the master has been shutdown properly (smart or fast mode), it will ensure that pending

Re: [GENERAL] Streaming replication, master recycling

I apologise for the missing data. we are running 9.1.15 on debian servers. when we promote the old slave, it seems to go fine. Are you saying that it will cause issues down the line if the previous master is not shut down before promoting? I was actually more concerned with the fact that we

Re: [GENERAL] Streaming replication, master recycling

On Wed, May 11, 2016 at 2:31 PM, wrote: > Hi All, > > we are currently using streaming replication on multiple node pairs. We > are seeing some issues, but I am mainly interrested in clarification. > > When a failover occurs, we touch the trigger file, promoting the

Re: [GENERAL] Invalid data read from synchronously replicated hot standby

Sameer Kumar wrote on 04/21/2016 13:56:52: > From: Sameer Kumar > To: Martin Kamp Jensen/DK/Schneider@Europe, pgsql-general@postgresql.org > Date: 04/21/2016 14:00 > Subject: Re: [GENERAL] Invalid data read from synchronously > replicated hot

Re: [GENERAL] Scaling Database for heavy load

On Wed, May 11, 2016 at 12:09 PM, Digit Penguin wrote: > Hello, > > > we use PostgreSql 9.x in conjunction with BIND/DNS for some Companies with > about 1.000 queries per second. > Now we have to scale the system up to 100.000 queries per second (about). > > Bind/DNS is

[GENERAL] Scaling Database for heavy load

Hello, we use PostgreSql 9.x in conjunction with BIND/DNS for some Companies with about 1.000 queries per second. Now we have to scale the system up to 100.000 queries per second (about). Bind/DNS is very light and i think can not give us bottleneck. The question is how to dimension the backend

Re: [GENERAL] Lightest way of checking if postgresql is running at the other end of an ssh tunnel?

On Wed, May 11, 2016 at 11:17:54AM +0200, Niels Kristian Schjødt wrote: > We have an ssh connection running from one server to our > postgresql database on another server. Some times we > experience that the ssh tunnel does not work anymore and > needs to be restarted, even though we use the

[GENERAL] Lightest way of checking if postgresql is running at the other end of an ssh tunnel?

Hi, We have an ssh connection running from one server to our postgresql database on another server. Some times we experience that the ssh tunnel does not work anymore and needs to be restarted, even though we use the autossh package. I would like to write a script that “pings” postgresql on