Re: Postgres DB crashing

Greetings !

I have a hierarchy table with two attributes : Parent_ID, Child_ID with the sample data (can go upto n-level) as below:

ParentID
ChildID


1
3


1
4


3
5


5
6


6
7

I need a query to retrieve all the n level hierarchy when a Child node_id is passed. For example; if I pass 7, then i need parent nodes as below: I want to achive this in postgres database (version 9.2) without connect by prior.

3 5
5 6
6 7

Any reply on this is great help.

Thanks and REgards
RAdha Krishna

Greetings !

I have a hierarchy table with two attributes : Parent_ID, Child_ID with the sample data (can go upto n-level) as below:

ParentID
ChildID


1
3


1
4


3
5


5
6


6
7

I need a query to retrieve all the n level hierarchy when a Child node_id is passed. For example; if I pass 7, then i need parent nodes as below: I want to achive this in postgres database (version 9.2) without connect by prior.

ParentID
ChildID
3 5
5 6
6 7

Any reply on this is great help.

Thanks and REgards
RAdha Krishna

On 03/01/2013 12:14 PM, bhanu udaya wrote:
>
> I need a query to retrieve all the n level hierarchy when a Child
> node_id is passed.
I think you're probably looking for the pgsql-general mailing list or
Stack Overflow. This list is focused on the PgAdmin-III program.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mar 1, 2013, at 3:18, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:

> I need a query to retrieve all the n level hierarchy when a Child node_id is passed. For example; if I pass 7, then i need parent nodes as below: I want to achive this in postgres database (version 9.2) without connect by prior.
>
> 3 5
> 5 6
> 6 7
>
> Any reply on this is great help.

You're probably looking for recursive common table expressions:
http://www.postgresql.org/docs/9.2/static/queries-with.html

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Hello,
Greetings.
My PostgresSQL (9.2) is crashing after certain load tests. Currently, postgressql is crashing when simulatenously 800 to 1000 threads are run on a 10 million records schema. Not sure, if we have to tweak some more parameters of postgres. Currently, the postgressql is configured as below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres limitation to support only 800 threads or any other configuration required. Please look at the log as below with errors. Please reply

max_connections
5000


shared_buffers
2024 MB


synchronous_commit
off


wal_buffers
100 MB


wal_writer_delays
1000ms


checkpoint_segments
512


checkpoint_timeout
5 min


checkpoint_completion_target
0.5


checkpoint_warning
30s


work_memory
1G


effective_cache_size
5 GB

2013-06-11 15:11:17 GMT [26201]: [1-1]ERROR: canceling autovacuum task

2013-06-11 15:11:17 GMT [26201]: [2-1]CONTEXT: automatic vacuum of table "newrelic.tenant1.customer"

2013-06-11 15:11:17 GMT [25242]: [1-1]LOG: sending cancel to blocking autovacuum PID 26201

2013-06-11 15:11:17 GMT [25242]: [2-1]DETAIL: Process 25242 waits for ExclusiveLock on extension of relation 679054 of database 666546.

2013-06-11 15:11:17 GMT [25242]: [3-1]STATEMENT: UPDATE tenant1.customer SET lastmodifieddate = $1 WHERE id IN ( select random_range((select min(id) from tenant1.customer ), (select max(id) from tenant1.customer )) as id ) AND softdeleteflag IS NOT TRUE

2013-06-11 15:11:17 GMT [25242]: [4-1]WARNING: could not send signal to process 26201: No such process

2013-06-11 15:22:29 GMT [22229]: [11-1]WARNING: worker took too long to start; canceled

2013-06-11 15:24:10 GMT [26511]: [1-1]WARNING: autovacuum worker started without a worker entry

2013-06-11 16:03:33 GMT [23092]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:06:05 GMT [23222]: [5-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:07:06 GMT [26869]: [1-1]FATAL: canceling authentication due to timeout

2013-06-11 16:23:16 GMT [25128]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:23:20 GMT [25128]: [2-1]LOG: unexpected EOF on client connection with an open transaction

2013-06-11 16:30:56 GMT [23695]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:43:55 GMT [24618]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:44:29 GMT [25204]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:54:14 GMT [22226]: [1-1]PANIC: stuck spinlock (0x2aaab54279d4) detected at bufmgr.c:1239

2013-06-11 16:54:14 GMT [32521]: [8-1]LOG: checkpointer process (PID 22226) was terminated by signal 6: Aborted

2013-06-11 16:54:14 GMT [32521]: [9-1]LOG: terminating any other active server processes

2013-06-11 16:54:14 GMT [26931]: [1-1]WARNING: terminating connection because of crash of another server process

2013-06-11 16:54:14 GMT [26931]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2013-06-11 16:54:14 GMT [26931]: [3-1]HINT: In a moment you should be able to reconnect to the database and repeat your command.

2013-06-11 16:54:14 GMT [26401]: [1-1]WARNING: terminating connection because of crash of another server process

2013-06-11 16:54:14 GMT [26401]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2013-06-11 16:55:08 GMT [27579]: [1-1]FATAL: the database system is in recovery mode

2013-06-11 16:55:08 GMT [24041]: [1-1]WARNING: terminating connection because of crash of another server process

2013-06-11 16:55:08 GMT [24041]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current

Stop the autovacuum process and try again.

On Tue, Jun 18, 2013 at 1:31 PM, bhanu udaya <udayabhanu1984(at)hotmail(dot)com>wrote:

> Hello,
> Greetings.
>
> My PostgresSQL (9.2) is crashing after certain load tests. Currently,
> postgressql is crashing when simulatenously 800 to 1000 threads are run on
> a 10 million records schema. Not sure, if we have to tweak some more
> parameters of postgres. Currently, the postgressql is configured as below
> on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres
> limitation to support only 800 threads or any other configuration required.
> Please look at the log as below with errors. Please reply
>
>
> max_connections 5000 shared_buffers 2024 MB synchronous_commit off
> wal_buffers 100 MB wal_writer_delays 1000ms checkpoint_segments 512
> checkpoint_timeout 5 min checkpoint_completion_target 0.5
> checkpoint_warning 30s work_memory 1G effective_cache_size 5 GB
>
>
>
> 2013-06-11 15:11:17 GMT [26201]: [1-1]ERROR: canceling autovacuum task
>
> 2013-06-11 15:11:17 GMT [26201]: [2-1]CONTEXT: automatic vacuum of table
> "newrelic.tenant1.customer"
>
> 2013-06-11 15:11:17 GMT [25242]: [1-1]LOG: sending cancel to blocking
> autovacuum PID 26201
>
> 2013-06-11 15:11:17 GMT [25242]: [2-1]DETAIL: Process 25242 waits for
> ExclusiveLock on extension of relation 679054 of database 666546.
>
> 2013-06-11 15:11:17 GMT [25242]: [3-1]STATEMENT: UPDATE tenant1.customer
> SET lastmodifieddate = $1 WHERE id IN ( select random_range((select min(id)
> from tenant1.customer ), (select max(id) from tenant1.customer )) as id )
> AND softdeleteflag IS NOT TRUE
>
> 2013-06-11 15:11:17 GMT [25242]: [4-1]WARNING: could not send signal to
> process 26201: No such process
>
> 2013-06-11 15:22:29 GMT [22229]: [11-1]WARNING: worker took too long to
> start; canceled
>
> 2013-06-11 15:24:10 GMT [26511]: [1-1]WARNING: autovacuum worker started
> without a worker entry
>
> 2013-06-11 16:03:33 GMT [23092]: [1-1]LOG: could not receive data from
> client: Connection timed out
>
> 2013-06-11 16:06:05 GMT [23222]: [5-1]LOG: could not receive data from
> client: Connection timed out
>
> 2013-06-11 16:07:06 GMT [26869]: [1-1]FATAL: canceling authentication due
> to timeout
>
> 2013-06-11 16:23:16 GMT [25128]: [1-1]LOG: could not receive data from
> client: Connection timed out
>
> 2013-06-11 16:23:20 GMT [25128]: [2-1]LOG: unexpected EOF on client
> connection with an open transaction
>
> 2013-06-11 16:30:56 GMT [23695]: [1-1]LOG: could not receive data from
> client: Connection timed out
>
> 2013-06-11 16:43:55 GMT [24618]: [1-1]LOG: could not receive data from
> client: Connection timed out
>
> 2013-06-11 16:44:29 GMT [25204]: [1-1]LOG: could not receive data from
> client: Connection timed out
>
> 2013-06-11 16:54:14 GMT [22226]: [1-1]PANIC: stuck spinlock
> (0x2aaab54279d4) detected at bufmgr.c:1239
>
> 2013-06-11 16:54:14 GMT [32521]: [8-1]LOG: checkpointer process (PID
> 22226) was terminated by signal 6: Aborted
>
> 2013-06-11 16:54:14 GMT [32521]: [9-1]LOG: terminating any other active
> server processes
>
> 2013-06-11 16:54:14 GMT [26931]: [1-1]WARNING: terminating connection
> because of crash of another server process
>
> 2013-06-11 16:54:14 GMT [26931]: [2-1]DETAIL: The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
>
> 2013-06-11 16:54:14 GMT [26931]: [3-1]HINT: In a moment you should be able
> to reconnect to the database and repeat your command.
>
> 2013-06-11 16:54:14 GMT [26401]: [1-1]WARNING: terminating connection
> because of crash of another server process
>
> 2013-06-11 16:54:14 GMT [26401]: [2-1]DETAIL: The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
>
> 2013-06-11 16:55:08 GMT [27579]: [1-1]FATAL: the database system is in
> recovery mode
>
> 2013-06-11 16:55:08 GMT [24041]: [1-1]WARNING: terminating connection
> because of crash of another server process
>
> 2013-06-11 16:55:08 GMT [24041]: [2-1]DETAIL: The postmaster has commanded
> this server process to roll back the current
>
>

On 6/18/2013 10:31 AM, bhanu udaya wrote:
> My PostgresSQL (9.2) is crashing after certain load tests. Currently,
> postgressql is crashing when simulatenously 800 to 1000 threads are
> run on a 10 million records schema. Not sure, if we have to tweak some
> more parameters of postgres. Currently, the postgressql is configured
> as below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this
> postgres limitation to support only 800 threads or any other
> configuration required. Please look at the log as below with errors.
> Please reply

thats an insanely high number of connections on a quad core processor.
in general, any more than 2-4X the number of cpu hardware threads in
concurrent queries is counterproductive and will result in higher
overhead and less throughput. if you have a real world use case for
800-1000 client threads and your workload is predominately short fast
transactions ("OLTP"), you should use a connection pooler like pgbouncer
and limit the number of active connections to something like 32 on your
4 core/8 thread CPU.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

Thanks for the quick response. These errors are after disabling the autovacuum. auto_vacuum parameter was set to off.
Can find the exact reason for this crash.

Thanks and Regards
Radha Krishna
Date: Tue, 18 Jun 2013 13:54:09 -0400
Subject: Re: [pgadmin-support] [GENERAL] Postgres DB crashing
From: rummandba(at)gmail(dot)com
To: udayabhanu1984(at)hotmail(dot)com
CC: kgrittn(at)mail(dot)com; adrian(dot)klaver(at)gmail(dot)com; pgsql-general(at)postgresql(dot)org; pgadmin-support(at)postgresql(dot)org; laurenz(dot)albe(at)wien(dot)gv(dot)at; chris(dot)travers(at)gmail(dot)com; magnus(at)hagander(dot)net

Stop the autovacuum process and try again.

On Tue, Jun 18, 2013 at 1:31 PM, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:

Hello,
Greetings.
My PostgresSQL (9.2) is crashing after certain load tests. Currently, postgressql is crashing when simulatenously 800 to 1000 threads are run on a 10 million records schema. Not sure, if we have to tweak some more parameters of postgres. Currently, the postgressql is configured as below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres limitation to support only 800 threads or any other configuration required. Please look at the log as below with errors. Please reply

max_connections
5000


shared_buffers
2024 MB


synchronous_commit
off


wal_buffers
100 MB


wal_writer_delays
1000ms


checkpoint_segments
512


checkpoint_timeout
5 min


checkpoint_completion_target
0.5


checkpoint_warning
30s


work_memory
1G


effective_cache_size
5 GB

2013-06-11 15:11:17 GMT [26201]: [1-1]ERROR: canceling autovacuum task

2013-06-11 15:11:17 GMT [26201]: [2-1]CONTEXT: automatic vacuum of table "newrelic.tenant1.customer"

2013-06-11 15:11:17 GMT [25242]: [1-1]LOG: sending cancel to blocking autovacuum PID 26201

2013-06-11 15:11:17 GMT [25242]: [2-1]DETAIL: Process 25242 waits for ExclusiveLock on extension of relation 679054 of database 666546.

2013-06-11 15:11:17 GMT [25242]: [3-1]STATEMENT: UPDATE tenant1.customer SET lastmodifieddate = $1 WHERE id IN ( select random_range((select min(id) from tenant1.customer ), (select max(id) from tenant1.customer )) as id ) AND softdeleteflag IS NOT TRUE

2013-06-11 15:11:17 GMT [25242]: [4-1]WARNING: could not send signal to process 26201: No such process

2013-06-11 15:22:29 GMT [22229]: [11-1]WARNING: worker took too long to start; canceled

2013-06-11 15:24:10 GMT [26511]: [1-1]WARNING: autovacuum worker started without a worker entry

2013-06-11 16:03:33 GMT [23092]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:06:05 GMT [23222]: [5-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:07:06 GMT [26869]: [1-1]FATAL: canceling authentication due to timeout

2013-06-11 16:23:16 GMT [25128]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:23:20 GMT [25128]: [2-1]LOG: unexpected EOF on client connection with an open transaction

2013-06-11 16:30:56 GMT [23695]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:43:55 GMT [24618]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:44:29 GMT [25204]: [1-1]LOG: could not receive data from client: Connection timed out

2013-06-11 16:54:14 GMT [22226]: [1-1]PANIC: stuck spinlock (0x2aaab54279d4) detected at bufmgr.c:1239

2013-06-11 16:54:14 GMT [32521]: [8-1]LOG: checkpointer process (PID 22226) was terminated by signal 6: Aborted

2013-06-11 16:54:14 GMT [32521]: [9-1]LOG: terminating any other active server processes

2013-06-11 16:54:14 GMT [26931]: [1-1]WARNING: terminating connection because of crash of another server process

2013-06-11 16:54:14 GMT [26931]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2013-06-11 16:54:14 GMT [26931]: [3-1]HINT: In a moment you should be able to reconnect to the database and repeat your command.

2013-06-11 16:54:14 GMT [26401]: [1-1]WARNING: terminating connection because of crash of another server process

2013-06-11 16:54:14 GMT [26401]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2013-06-11 16:55:08 GMT [27579]: [1-1]FATAL: the database system is in recovery mode

2013-06-11 16:55:08 GMT [24041]: [1-1]WARNING: terminating connection because of crash of another server process

2013-06-11 16:55:08 GMT [24041]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current

On 18/06/13 18:31, bhanu udaya wrote:
> Hello,
> Greetings.
>
> My PostgresSQL (9.2) is crashing after certain load tests. Currently,
> postgressql is crashing when simulatenously 800 to 1000 threads are run
> on a 10 million records schema. Not sure, if we have to tweak some more
> parameters of postgres. Currently, the postgressql is configured as
> below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres
> limitation to support only 800 threads or any other configuration
> required. Please look at the log as below with errors. Please reply
>
>
> max_connections 5000
> shared_buffers 2024 MB
> synchronous_commit off
> wal_buffers 100 MB
> wal_writer_delays 1000ms
> checkpoint_segments 512
> checkpoint_timeout 5 min
> checkpoint_completion_target 0.5
> checkpoint_warning 30s
> work_memory 1G
> effective_cache_size 5 GB

Just to point out, your memory settings are set to allow *at least*

shared-buffers 2GB + (5000 * 1GB) = 5TB+

You don't have that much memory. You probably don't have that much disk.
This is never going to work.

As has been said, there's no way you can do useful work simultaneously
with 1000 threads if you only have 4 cores - use a connection pooler.
You'll also need to reduce work_mem to 1MB or so.

--
Richard Huxton
Archonet Ltd

On Thu, Jun 20, 2013 at 5:17 AM, Richard Huxton <dev(at)archonet(dot)com> wrote:
> On 18/06/13 18:31, bhanu udaya wrote:
>>
>> Hello,
>> Greetings.
>>
>> My PostgresSQL (9.2) is crashing after certain load tests. Currently,
>> postgressql is crashing when simulatenously 800 to 1000 threads are run
>> on a 10 million records schema. Not sure, if we have to tweak some more
>> parameters of postgres. Currently, the postgressql is configured as
>> below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres
>> limitation to support only 800 threads or any other configuration
>> required. Please look at the log as below with errors. Please reply
>>
>>
>> max_connections 5000
>> shared_buffers 2024 MB
>> synchronous_commit off
>> wal_buffers 100 MB
>> wal_writer_delays 1000ms
>> checkpoint_segments 512
>> checkpoint_timeout 5 min
>> checkpoint_completion_target 0.5
>> checkpoint_warning 30s
>> work_memory 1G
>> effective_cache_size 5 GB
>
>
> Just to point out, your memory settings are set to allow *at least*
>
> shared-buffers 2GB + (5000 * 1GB) = 5TB+
>
> You don't have that much memory. You probably don't have that much disk.
> This is never going to work.
>
> As has been said, there's no way you can do useful work simultaneously with
> 1000 threads if you only have 4 cores - use a connection pooler. You'll also
> need to reduce work_mem to 1MB or so.

aside: if you have particular query that needs extra work_mem, you can
always temporarily raise it at run time (unlike shared buffers).

OP needs to explore use of connection pooler, in particular pgbouncer.
Anyways none of this explains why the server is actually crashing.

merlin

On Thursday, June 20, 2013 07:52:21 AM Merlin Moncure wrote:
> OP needs to explore use of connection pooler, in particular pgbouncer.
> Anyways none of this explains why the server is actually crashing.

It might be hitting file descriptor limits. I didn't dig into the earlier part
of this thread much, though.

Alan Hodgson <ahodgson(at)simkin(dot)ca> writes:
> On Thursday, June 20, 2013 07:52:21 AM Merlin Moncure wrote:
>> OP needs to explore use of connection pooler, in particular pgbouncer.
>> Anyways none of this explains why the server is actually crashing.

> It might be hitting file descriptor limits. I didn't dig into the earlier part
> of this thread much, though.

The disturbing part of the original report was this:

>>> 2013-06-11 16:54:14 GMT [22226]: [1-1]PANIC: stuck spinlock (0x2aaab54279d4) detected at bufmgr.c:1239

which implies that something was holding a buffer header spinlock for an
unreasonably long time (roughly 2 minutes, when no operation that holds
such a lock should take more than a few nanoseconds). But if you were
running a load test that absolutely mashed the machine into the ground,
as the OP seems to have been doing, maybe that could happen --- perhaps
some unlucky backend got interrupted and then swapped out during the
narrow window where it held such a lock, and the machine was too
overloaded to give that process any more cycles for a very long time.

As has been noted already, this test setup seems to have overloaded the
machine by at least two orders of magnitude compared to useful settings
for the available hardware. The "stuck spinlock" error would only come
out if a lock had been held for quite a lot more than two orders of
magnitude more time than expected, though. So I'm not entirely sure
that I buy this theory; but it's hard to see another one. (I discount
the obvious other theory that there's a software bug, because I just
looked through 9.2's bufmgr.c very carefully, and there are no code
paths where it fails to release a buffer header lock within a very few
instructions from where it took the lock.)

regards, tom lane

Hello,

Grettings,

What is the best way of doing case insensitive searches in postgres using Like.

Ilike - does not use indexes
function based indexes are not as fast as required.
CITEXT - it still taking 600 ms - 1 second on a 2.2 million rows... does not use index
Collation Indexes creation with POSIX - does not really work.
GIST/GIN indexes are faster when using like, but not case insenstive.

Is there a better way of resolving this case insenstive searches with fast retrieval.

Thanks and Regards
Radha Krishna

bhanu udaya wrote:
> What is the best way of doing case insensitive searches in postgres using Like.

Table "laurenz.t"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
val | text | not null
Indexes:
"t_pkey" PRIMARY KEY, btree (id)

CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops);

ANALYZE t;

EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%';

QUERY PLAN
------------------------------------------------------------------------------
Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4)
Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text))
Filter: (upper(val) ~~ 'AB%'::text)
(3 rows)

Yours,
Laurenz Albe

Thanks. But, I do not want to convert into upper and show the result.
Example, if I have records as below:
id type
1. abcd
2. Abcdef
3. ABcdefg
4. aaadf

The below query should report all the above

select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type & collation POSIX, but it did not really help.

Thanks and Regards
Radha Krishna

> From: laurenz(dot)albe(at)wien(dot)gv(dot)at
> To: udayabhanu1984(at)hotmail(dot)com; pgsql-general(at)postgresql(dot)org
> Subject: RE: Postgres case insensitive searches
> Date: Fri, 28 Jun 2013 12:32:00 +0000
>
> bhanu udaya wrote:
> > What is the best way of doing case insensitive searches in postgres using Like.
>
> Table "laurenz.t"
> Column | Type | Modifiers
> --------+---------+-----------
> id | integer | not null
> val | text | not null
> Indexes:
> "t_pkey" PRIMARY KEY, btree (id)
>
>
> CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops);
>
> ANALYZE t;
>
> EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%';
>
> QUERY PLAN
> ------------------------------------------------------------------------------
> Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4)
> Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text))
> Filter: (upper(val) ~~ 'AB%'::text)
> (3 rows)
>
> Yours,
> Laurenz Albe

On 6/28/2013 6:59 PM, bhanu udaya wrote:
> select * from table where type like 'ab%'. It should get all above 3
> records. Is there a way the database itself can be made
> case-insensitive with UTF8 characterset. I tried with character type &
> collation POSIX, but it did not really help.

use ILIKE

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On Jun 29, 2013, at 3:59, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:

> Thanks. But, I do not want to convert into upper and show the result.

Why not? It won't modify your results, just the search condition:

SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val;

Or:

SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY upper(val), val;

> Example, if I have records as below:
> id type
> 1. abcd
> 2. Abcdef
> 3. ABcdefg
> 4. aaadf
>
> The below query should report all the above
>
> select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type & collation POSIX, but it did not really help.

I was under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS collation mechanics). So, what OS are you running the server on?

> > From: laurenz(dot)albe(at)wien(dot)gv(dot)at
> > To: udayabhanu1984(at)hotmail(dot)com; pgsql-general(at)postgresql(dot)org
> > Subject: RE: Postgres case insensitive searches
> > Date: Fri, 28 Jun 2013 12:32:00 +0000

Please do not top-post on this list.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Hello,
I agree that it is just search condition. But, in a 2.5 million record table search, upper function is not that fast. The expectation is to get the query retrieved in 100 ms...with all indexes used.

I tried with upper, Citext, but the result set was more than a second.

The OS server we are using is Linux 64 bit.

Thanks and Regards
Radha Krishna

> Subject: Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches
> From: haramrae(at)gmail(dot)com
> Date: Sat, 29 Jun 2013 09:37:51 +0200
> CC: laurenz(dot)albe(at)wien(dot)gv(dot)at; pgsql-general(at)postgresql(dot)org; pgadmin-support(at)postgresql(dot)org
> To: udayabhanu1984(at)hotmail(dot)com
>
> On Jun 29, 2013, at 3:59, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:
>
> > Thanks. But, I do not want to convert into upper and show the result.
>
> Why not? It won't modify your results, just the search condition:
>
> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val;
>
> Or:
>
> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY upper(val), val;
>
>
> > Example, if I have records as below:
> > id type
> > 1. abcd
> > 2. Abcdef
> > 3. ABcdefg
> > 4. aaadf
> >
> > The below query should report all the above
> >
> > select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type & collation POSIX, but it did not really help.
>
> I was under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS collation mechanics). So, what OS are you running the server on?
>
> > > From: laurenz(dot)albe(at)wien(dot)gv(dot)at
> > > To: udayabhanu1984(at)hotmail(dot)com; pgsql-general(at)postgresql(dot)org
> > > Subject: RE: Postgres case insensitive searches
> > > Date: Fri, 28 Jun 2013 12:32:00 +0000
>
> Please do not top-post on this list.
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support

On Jun 29, 2013, at 15:02, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:

> I agree that it is just search condition. But, in a 2.5 million record table search, upper function is not that fast.

Suit yourself, the solution is there.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
On 06/29/2013 09:02 AM, bhanu udaya wrote:<br>
<blockquote cite="mid:COL127-W1529DEA2A427BDB5A28C9FD3770(at)phx(dot)gbl"
type="cite">
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style>
<div dir="ltr">Hello,<br>
I agree that it is just search condition. But, in a&nbsp;2.5 million
record table search, upper function is not that fast.&nbsp; The
expectation is to get the query retrieved in 100 ms...with all
indexes used.<br>
&nbsp;<br>
I tried with upper, Citext, but the result set was more than a
second.<br>
&nbsp;<br>
The OS server we are using is Linux 64 bit.<br>
&nbsp;<br>
Thanks and Regards<br>
Radha Krishna<br>
&nbsp;<br>
<div>&gt; Subject: Re: [pgadmin-support] [GENERAL] Postgres case
insensitive searches<br>
&gt; From: <a class="moz-txt-link-abbreviated" href="mailto:haramrae(at)gmail(dot)com">haramrae(at)gmail(dot)com</a><br>
&gt; Date: Sat, 29 Jun 2013 09:37:51 +0200<br>
&gt; CC: <a class="moz-txt-link-abbreviated" href="mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at">laurenz(dot)albe(at)wien(dot)gv(dot)at</a>;
<a class="moz-txt-link-abbreviated" href="mailto:pgsql-general(at)postgresql(dot)org">pgsql-general(at)postgresql(dot)org</a>; <a class="moz-txt-link-abbreviated" href="mailto:pgadmin-support(at)postgresql(dot)org">pgadmin-support(at)postgresql(dot)org</a><br>
&gt; To: <a class="moz-txt-link-abbreviated" href="mailto:udayabhanu1984(at)hotmail(dot)com">udayabhanu1984(at)hotmail(dot)com</a><br>
&gt; <br>
&gt; On Jun 29, 2013, at 3:59, bhanu udaya
<a class="moz-txt-link-rfc2396E" href="mailto:udayabhanu1984(at)hotmail(dot)com">&lt;udayabhanu1984(at)hotmail(dot)com&gt;</a> wrote:<br>
&gt; <br>
&gt; &gt; Thanks. But, I do not want to convert into upper and
show the result. <br>
&gt; <br>
&gt; Why not? It won't modify your results, just the search
condition:<br>
&gt; <br>
&gt; SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BY val;<br>
&gt; <br>
&gt; Or:<br>
&gt; <br>
&gt; SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BY upper(val), val;<br>
&gt; <br>
&gt; <br>
&gt; &gt; Example, if I have records as below:<br>
&gt; &gt; id type<br>
&gt; &gt; 1. abcd<br>
&gt; &gt; 2. Abcdef<br>
&gt; &gt; 3. ABcdefg<br>
&gt; &gt; 4. aaadf<br>
&gt; &gt; <br>
&gt; &gt; The below query should report all the above <br>
&gt; &gt; <br>
&gt; &gt; select * from table where type like 'ab%'. It should
get all above 3 records. Is there a way the database itself
can be made case-insensitive with UTF8 characterset. I tried
with character type &amp; collation POSIX, but it did not
really help.<br>
&gt; <br>
&gt; I was under the impression this would work, but ISTR that
not every OS has this capability (Postgres makes use of the OS
collation mechanics). So, what OS are you running the server
on?<br>
&gt; <br>
</div>
</div>
</blockquote>
<br>
Duplicate the column with an upper or lowercase version and run all
queries against that.<br>
<br>
CREATE TABLE foo (<br>
&nbsp;&nbsp;&nbsp; id serial PRIMARY KEY,<br>
&nbsp;&nbsp;&nbsp; val text,<br>
&nbsp;&nbsp;&nbsp; val_lower text<br>
);<br>
<br>
Index val_lower. Use triggers to keep val and val_lower in sync and
discard all attempts to write directly to val_lower. Then all
queries would be of the form<br>
<br>
SELECT id, val<br>
FROM foo<br>
WHERE val_lower LIKE 'ab%';<br>
<br>
Wouldn't want to write every table like this, but if (a) query speed
trumps all other requirements and (b) functional index, CITEXT, etc.
have all been rejected as not fast enough&#8230;<br>
<br>
--Lee<br>
<br>
<br>
<pre class="moz-signature" cols="72">--
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
<a class="moz-txt-link-freetext" href="http://freecity.commons.gc.cuny.edu">http://freecity.commons.gc.cuny.edu</a>
</pre>
</body>
</html>

On 06/28/2013 03:21 AM, bhanu udaya wrote:
> Hello,
>
> Grettings,
>
> What is the best way of doing case insensitive searches in postgres
> using Like.
>
> Ilike - does not use indexes
> function based indexes are not as fast as required.
> CITEXT - it still taking 600 ms - 1 second on a 2.2 million rows... does
> not use index
> Collation Indexes creation with POSIX - does not really work.
> GIST/GIN indexes are faster when using like, but not case insenstive.
>
> Is there a better way of resolving this case insenstive searches with
> fast retrieval.

O.k. there is not anywhere near enough information here to provide you
with a proper answer but here are the two things you should look at:

CITEXT: You said it takes 600ms - 1 second. Is that a first run or is
the relation cached? Second how do you know it isn't using the index?
Have you ran an explain analyze? In order for CITEXT to use an index it
the value being searched must be the PRIMARY KEY, is your column the
primary key?

Second, you have provided us with zero information on your hardware
configuration. 2.2 million rows is a low of rows to seqscan, if they
aren't cached or if you don't have reasonable hardware it is going to
take time no matter what you do.

Third, have you tried this with unlogged tables (for performance)?

Fourth, there was another person that suggested using UPPER() that is a
reasonable suggestion. The docs clearly suggest using lower(), I don't
actually know if there is a difference but that is the common way to do
it and it will use an index IF you make a functional index on the column
using lower.

JD

>
> Thanks and Regards
> Radha Krishna
>

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

> Subject: Re: [pgadmin-support] [GENERAL] Postgres case
insensitive searches

> From: haramrae(at)gmail(dot)com

> Date: Sat, 29 Jun 2013 09:37:51 +0200

> CC: laurenz(dot)albe(at)wien(dot)gv(dot)at;
pgsql-general(at)postgresql(dot)org; pgadmin-support(at)postgresql(dot)org

> To: udayabhanu1984(at)hotmail(dot)com

>

> On Jun 29, 2013, at 3:59, bhanu udaya
<udayabhanu1984(at)hotmail(dot)com> wrote:

>

> > Thanks. But, I do not want to convert into upper and
show the result.

>

> Why not? It won't modify your results, just the search
condition:

>

> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BY val;

>

> Or:

>

> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BY upper(val), val;

>

>

> > Example, if I have records as below:

> > id type

> > 1. abcd

> > 2. Abcdef

> > 3. ABcdefg

> > 4. aaadf

> >

> > The below query should report all the above

> >

> > select * from table where type like 'ab%'. It should
get all above 3 records. Is there a way the database itself
can be made case-insensitive with UTF8 characterset. I tried
with character type & collation POSIX, but it did not
really help.

>

> I was under the impression this would work, but ISTR that
not every OS has this capability (Postgres makes use of the OS
collation mechanics). So, what OS are you running the server
on?

>

Duplicate the column with an upper or lowercase version and run all
queries against that.

CREATE TABLE foo (

id serial PRIMARY KEY,

val text,

val_lower text

);

Index val_lower. Use triggers to keep val and val_lower in sync and
discard all attempts to write directly to val_lower. Then all
queries would be of the form

SELECT id, val

FROM foo

WHERE val_lower LIKE 'ab%';

Wouldn't want to write every table like this, but if (a) query speed
trumps all other requirements and (b) functional index, CITEXT, etc.
have all been rejected as not fast enough…

--Lee

--
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu
It is a good idea to have a duplicate column and index and use that column. But, we have heavyinserts/updates on this table. I am afraid that it would slow down the insert performance. But, I would definately like to test this option. Isn't it better to convert Postgres DB to case insensitive ?How difficult is that ? I want the DB to support UTF8 and be case insensitive like SQL Server. Thanks

Yes. I have used analyze table, and also I have explain plan the CITEXT query. It was not using Index. It is not primary and it is surprised to know that CITEXT would use index only if it is a primary key column. Interesting and new thing to know.

Upper and Lower functions are not right choice when the table is > 2.5 million and where we also have heavy insert transactions.

I doubt, if we can cache the table if there are frequent inserts/updates. The good idea would be to get the DB to case insenstive configuration like SQL Server. I would go for this solution, if postgres supports.

Thanks for all the replies and help.

> Date: Sat, 29 Jun 2013 09:02:12 -0700
> From: jd(at)commandprompt(dot)com
> To: udayabhanu1984(at)hotmail(dot)com
> CC: kgrittn(at)mail(dot)com; adrian(dot)klaver(at)gmail(dot)com; pgsql-general(at)postgresql(dot)org; pgadmin-support(at)postgresql(dot)org; laurenz(dot)albe(at)wien(dot)gv(dot)at; chris(dot)travers(at)gmail(dot)com; magnus(at)hagander(dot)net
> Subject: Re: [GENERAL] Postgres case insensitive searches
>
>
> On 06/28/2013 03:21 AM, bhanu udaya wrote:
> > Hello,
> >
> > Grettings,
> >
> > What is the best way of doing case insensitive searches in postgres
> > using Like.
> >
> > Ilike - does not use indexes
> > function based indexes are not as fast as required.
> > CITEXT - it still taking 600 ms - 1 second on a 2.2 million rows... does
> > not use index
> > Collation Indexes creation with POSIX - does not really work.
> > GIST/GIN indexes are faster when using like, but not case insenstive.
> >
> > Is there a better way of resolving this case insenstive searches with
> > fast retrieval.
>
> O.k. there is not anywhere near enough information here to provide you
> with a proper answer but here are the two things you should look at:
>
> CITEXT: You said it takes 600ms - 1 second. Is that a first run or is
> the relation cached? Second how do you know it isn't using the index?
> Have you ran an explain analyze? In order for CITEXT to use an index it
> the value being searched must be the PRIMARY KEY, is your column the
> primary key?
> > Second, you have provided us with zero information on your hardware
> configuration. 2.2 million rows is a low of rows to seqscan, if they
> aren't cached or if you don't have reasonable hardware it is going to
> take time no matter what you do.
>
> Third, have you tried this with unlogged tables (for performance)?
>
> Fourth, there was another person that suggested using UPPER() that is a
> reasonable suggestion. The docs clearly suggest using lower(), I don't
> actually know if there is a difference but that is the common way to do
> it and it will use an index IF you make a functional index on the column
> using lower.
>
> JD
>
>
>
>
> >
> > Thanks and Regards
> > Radha Krishna
> >
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> For my dreams of your image that blossoms
> a rose in the deeps of my heart. - W.B. Yeats

On 6/29/2013 9:24 AM, bhanu udaya wrote:
> Upper and Lower functions are not right choice when the table is > 2.5
> million and where we also have heavy insert transactions.
>
> I doubt, if we can cache the table if there are frequent
> inserts/updates. The good idea would be to get the DB to case
> insenstive configuration like SQL Server. I would go for this
> solution, if postgres supports.

you need an INDEX on lower(field) or upper(field). this is only
computed when values are inserted.

if you like a specific feature of SQL Server, then by all means, use SQL
Server. postgres does not and will not support automatic case
insensitive data.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On 06/29/2013 09:24 AM, bhanu udaya wrote:

> Upper and Lower functions are not right choice when the table is > 2.5
> million and where we also have heavy insert transactions.

Prove it. Seriously, just run a test case against it. See how it works
for you. Inserts are generally a very inexpensive operation with Postgres.

>
> I doubt, if we can cache the table if there are frequent
> inserts/updates. The good idea would be to get the DB to case
> insenstive configuration like SQL Server. I would go for this solution,
> if postgres supports.

Postgres does not.

And as Jon said, maybe Postgres isn't the right solution for you. That
would be a bummer but we can't be all things to all people.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

On Jun 29, 2013, at 11:24 AM, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:

> Upper and Lower functions are not right choice when the table is > 2.5 million and where we also have heavy insert transactions.

PostgreSQL and SQL Server are completely different. Rules that apply to SQL Server do not necessarily apply to PostgreSQL.

You problem is not the use of upper() or lower() it is the assumption what works in SQL Server is the best way to use PostgreSQL. You'll get farther if you benchmark several of the suggestions, then if the performance is not good enough, ask how to improve the performance. This will take a little work on your part, but that is how you learn.

Neil

I have a table called jobs with ~17 millions records. Without an index on
the queue column, the following query

select count(*) from jobs where lower(queue) = 'normal'

found ~2.6 millions records in 10160ms

With the following index:

create index lower_queue on jobs (lower(queue))

the same query only took 3850ms

On Sat, Jun 29, 2013 at 2:08 PM, Joshua D. Drake <jd(at)commandprompt(dot)com>wrote:

>
> On 06/29/2013 09:24 AM, bhanu udaya wrote:
>
> Upper and Lower functions are not right choice when the table is > 2.5
>> million and where we also have heavy insert transactions.
>>
>
> Prove it. Seriously, just run a test case against it. See how it works for
> you. Inserts are generally a very inexpensive operation with Postgres.
>
>
>> I doubt, if we can cache the table if there are frequent
>> inserts/updates. The good idea would be to get the DB to case
>> insenstive configuration like SQL Server. I would go for this solution,
>> if postgres supports.
>>
>
> Postgres does not.
>
> And as Jon said, maybe Postgres isn't the right solution for you. That
> would be a bummer but we can't be all things to all people.
>
>
> JD
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> For my dreams of your image that blossoms
> a rose in the deeps of my heart. - W.B. Yeats
>
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support(at)postgresql(dot)**org<pgadmin-support(at)postgresql(dot)org>
> )
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgadmin-support<http://www.postgresql.org/mailpref/pgadmin-support>
>

I almost used every option ; upper, posix, gist, gin, citext, etc. feature of the postgres to get the query most optimal.. If a particular query is taking 1 + second for one user/thread, then for many users accessing it concurrently would take lot of resources and the performance would be dropped in no time may be for 10 users .. I am trying to get the best way of achieving things with postgres.

I do not know what else can be done to get the performance more optimal. if there are any good suggestions in tweaking db parameters or with some index that can help, then I would love to experiment it and achieve it.

We have observed that inserts are ok, but the selects are dropping performance and not acceptable. Show me an index that can retrieve a simple select query (case insensitive) in 100 -200 ms. which has 2- 10 million records. Is this possible ? I could have gone for partitions, etc., but it is plan B and more over partitions in postgres has to undergo more manual process.

Thanks for all replies and help.
Subject: Re: [GENERAL] Postgres case insensitive searches
From: neilt(at)neiltiffin(dot)com
Date: Sat, 29 Jun 2013 14:08:47 -0500
CC: pgsql-general(at)postgresql(dot)org
To: udayabhanu1984(at)hotmail(dot)com

On Jun 29, 2013, at 11:24 AM, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:Upper and Lower functions are not right choice when the table is > 2.5 million and where we also have heavy insert transactions.
PostgreSQL and SQL Server are completely different. Rules that apply to SQL Server do not necessarily apply to PostgreSQL.
You problem is not the use of upper() or lower() it is the assumption what works in SQL Server is the best way to use PostgreSQL. You'll get farther if you benchmark several of the suggestions, then if the performance is not good enough, ask how to improve the performance. This will take a little work on your part, but that is how you learn.
Neil

I almost used every option ; upper, posix, gist, gin, citext, etc. feature of the postgres to get the query most optimal.. If a particular query is taking 1 + second for one user/thread, then for many users accessing it concurrently would take lot of resources and the performance would be dropped in no time may be for 10 users .. I am trying to get the best way of achieving things with postgres.

I do not know what else can be done to get the performance more optimal. if there are any good suggestions in tweaking db parameters or with some index that can help, then I would love to experiment it and achieve it.

We have observed that inserts are ok, but the selects are dropping performance and not acceptable. Show me an index that can retrieve a simple select query (case insensitive) in 100 -200 ms. from a table which has 2- 10 million records. Is this possible ? I could have gone for partitions, etc., but it is plan B and more over partitions in postgres has to undergo more manual process.

Thanks for all replies and help.
Subject: Re: [GENERAL] Postgres case insensitive searches
From: neilt(at)neiltiffin(dot)com
Date: Sat, 29 Jun 2013 14:08:47 -0500
CC: pgsql-general(at)postgresql(dot)org
To: udayabhanu1984(at)hotmail(dot)com

On Jun 29, 2013, at 11:24 AM, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:Upper and Lower functions are not right choice when the table is > 2.5 million and where we also have heavy insert transactions.
PostgreSQL and SQL Server are completely different. Rules that apply to SQL Server do not necessarily apply to PostgreSQL.
You problem is not the use of upper() or lower() it is the assumption what works in SQL Server is the best way to use PostgreSQL. You'll get farther if you benchmark several of the suggestions, then if the performance is not good enough, ask how to improve the performance. This will take a little work on your part, but that is how you learn.
Neil

Create database with UTF8 character with Collation Posix.
Also, modified the table column as below:
alter table tableA alter column colA type text COLLATE POSIX
create Index btree index on ColA Collate POSIX
Use the query lower(colA) like 'b%'
The results seems promissing. But, would like to do more research and come to conclusion.

From: udayabhanu1984(at)hotmail(dot)com
To: neilt(at)neiltiffin(dot)com
CC: pgsql-general(at)postgresql(dot)org
Subject: RE: [GENERAL] Postgres case insensitive searches
Date: Sun, 30 Jun 2013 22:35:32 +0530

I almost used every option ; upper, posix, gist, gin, citext, etc. feature of the postgres to get the query most optimal.. If a particular query is taking 1 + second for one user/thread, then for many users accessing it concurrently would take lot of resources and the performance would be dropped in no time may be for 10 users .. I am trying to get the best way of achieving things with postgres.

I do not know what else can be done to get the performance more optimal. if there are any good suggestions in tweaking db parameters or with some index that can help, then I would love to experiment it and achieve it.

We have observed that inserts are ok, but the selects are dropping performance and not acceptable. Show me an index that can retrieve a simple select query (case insensitive) in 100 -200 ms. from a table which has 2- 10 million records. Is this possible ? I could have gone for partitions, etc., but it is plan B and more over partitions in postgres has to undergo more manual process.

Thanks for all replies and help.
Subject: Re: [GENERAL] Postgres case insensitive searches
From: neilt(at)neiltiffin(dot)com
Date: Sat, 29 Jun 2013 14:08:47 -0500
CC: pgsql-general(at)postgresql(dot)org
To: udayabhanu1984(at)hotmail(dot)com

On Jun 29, 2013, at 11:24 AM, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:Upper and Lower functions are not right choice when the table is > 2.5 million and where we also have heavy insert transactions.
PostgreSQL and SQL Server are completely different. Rules that apply to SQL Server do not necessarily apply to PostgreSQL.
You problem is not the use of upper() or lower() it is the assumption what works in SQL Server is the best way to use PostgreSQL. You'll get farther if you benchmark several of the suggestions, then if the performance is not good enough, ask how to improve the performance. This will take a little work on your part, but that is how you learn.
Neil

On Jun 30, 2013 7:07 PM, "bhanu udaya" <udayabhanu1984(at)hotmail(dot)com> wrote:
>
>
> I almost used every option ; upper, posix, gist, gin, citext, etc.
feature of the postgres to get the query most optimal.. If a particular
query is taking 1 + second for one user/thread, then for many users
accessing it concurrently would take lot of resources and the performance
would be dropped in no time may be for 10 users .. I am trying to get the
best way of achieving things with postgres.
>
> I do not know what else can be done to get the performance more optimal.
if there are any good suggestions in tweaking db parameters or with some
index that can help, then I would love to experiment it and achieve it.
>
> We have observed that inserts are ok, but the selects are dropping
performance and not acceptable. Show me an index that can retrieve a
simple select query (case insensitive) in 100 -200 ms. from a table which
has 2- 10 million records. Is this possible ? I could have gone for
partitions, etc., but it is plan B and more over partitions in postgres has
to undergo more manual process.
>

How many rows are in the result? Can you use a partial index? What's the
usage pattern? Can you cache the result in a materialized view?

In general, getting one row from an index from a table that fits in your
RAM is possible in a few ms. Case insensitive or not.

Can you show us a explain analyze.

>
> Thanks for all replies and help.
> ________________________________
> Subject: Re: [GENERAL] Postgres case insensitive searches
> From: neilt(at)neiltiffin(dot)com
> Date: Sat, 29 Jun 2013 14:08:47 -0500
> CC: pgsql-general(at)postgresql(dot)org
> To: udayabhanu1984(at)hotmail(dot)com
>
>
> On Jun 29, 2013, at 11:24 AM, bhanu udaya <udayabhanu1984(at)hotmail(dot)com>
wrote:
>
>> Upper and Lower functions are not right choice when the table is > 2.5
million and where we also have heavy insert transactions.
>
>
> PostgreSQL and SQL Server are completely different. Rules that apply to
SQL Server do not necessarily apply to PostgreSQL.
>
> You problem is not the use of upper() or lower() it is the assumption
what works in SQL Server is the best way to use PostgreSQL. You'll get
farther if you benchmark several of the suggestions, then if the
performance is not good enough, ask how to improve the performance. This
will take a little work on your part, but that is how you learn.
>
> Neil

bhanu udaya wrote:
>>> What is the best way of doing case insensitive searches in postgres using Like.
>>
>> Table "laurenz.t"
>> Column | Type | Modifiers
>> --------+---------+-----------
>> id | integer | not null
>> val | text | not null
>> Indexes:
>> "t_pkey" PRIMARY KEY, btree (id)
>>
>>
>> CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops);
>>
>> ANALYZE t;
>>
>> EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%';
>>
>> QUERY PLAN
>> ------------------------------------------------------------------------------
>> Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4)
>> Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text))
>> Filter: (upper(val) ~~ 'AB%'::text)
>> (3 rows)

> Thanks. But, I do not want to convert into upper and show the result.
> Example, if I have records as below:
> id type
> 1. abcd
> 2. Abcdef
> 3. ABcdefg
> 4. aaadf
>
> The below query should report all the above

No, it shouldn't :^)

> select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the
> database itself can be made case-insensitive with UTF8 characterset. I tried with character type &
> collation POSIX, but it did not really help.

My solution is fast and efficient, it will call upper() only once
per query. I don't see your problem. Different database systems
do things in different ways, but as long as you can do what you need
to do, that should be good enough.

Yours,
Laurenz Albe

On Mon, Jul 1, 2013 at 10:01 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> bhanu udaya wrote:
>>>> What is the best way of doing case insensitive searches in postgres using Like.
>>>
>>> Table "laurenz.t"
>>> Column | Type | Modifiers
>>> --------+---------+-----------
>>> id | integer | not null
>>> val | text | not null
>>> Indexes:
>>> "t_pkey" PRIMARY KEY, btree (id)
>>>
>>>
>>> CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops);
>>>
>>> ANALYZE t;
>>>
>>> EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%';
>>>
>>> QUERY PLAN
>>> ------------------------------------------------------------------------------
>>> Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4)
>>> Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text))
>>> Filter: (upper(val) ~~ 'AB%'::text)
>>> (3 rows)
>

>
> My solution is fast and efficient, it will call upper() only once
> per query. I don't see your problem. Different database systems
> do things in different ways, but as long as you can do what you need
> to do, that should be good enough.
>
> Yours,
> Laurenz Albe
>

I was toying around a little bit with this example, just for my
understanding, the function upper is called for every row in the
result. I think this has something to to with the filter in the plan.
This is what I did

create table foo as (select md5(random()::text) from
generate_series(1,2.5e6::integer));
-- create a little wrapper function to see when it is called
create ': create or replace function test_upper(text_in TEXT) RETURNS TEXT AS
$func$
begin
raise warning 'called';
return upper(text_in);
end;
$func$ LANGUAGE plpgsql IMMUTABLE;

create index foo_ind on foo (test_upper(md5) text_pattern_ops); --lots
of 'called' ouptut
analyze foo;

-- here you see that the function is called for every row in the result
postgres=# select * from foo where test_upper(md5) like 'ABAAB%';
WARNING: called
WARNING: called
WARNING: called
md5
----------------------------------
abaab10ff1690418d69c360d2dc9c8fc
abaab339fb14a7a10324f6007d35599a
abaab34f0cebabee89fa222bfee7b6ea
(3 rows)

postgres=# explain select * from foo where test_upper(md5) like 'ABAAB%';
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using foo_ind on foo (cost=0.50..14.02 rows=250 width=33)
Index Cond: ((test_upper(md5) ~>=~ 'ABAAB'::text) AND
(test_upper(md5) ~<~ 'ABAAC'::text))
Filter: (test_upper(md5) ~~ 'ABAAB%'::text)
(3 rows)

So under my assumption that it is the filter that causes the function
execution, I don't understand
how a row can satisfy

--which I read as >= 'ABAAB' and < 'ABAAC'
((test_upper(md5) ~>=~ 'ABAAB'::text) AND (test_upper(md5) ~<~ 'ABAAC'::text))

and not

(test_upper(md5) ~~ 'ABAAB%'::text)

Ingmar

Ingmar Brouns wrote:
>> My solution is fast and efficient, it will call upper() only once
>> per query. I don't see your problem. Different database systems
>> do things in different ways, but as long as you can do what you need
>> to do, that should be good enough.

> I was toying around a little bit with this example, just for my
> understanding, the function upper is called for every row in the
> result. I think this has something to to with the filter in the plan.

You are right, and the function is also called once per
result row. The point I was really trying to make is that
it is *not* called once per row in the table.

> postgres=# explain select * from foo where test_upper(md5) like 'ABAAB%';
> QUERY PLAN
> ----------------------------------------------------------------------------------------------
> Index Scan using foo_ind on foo (cost=0.50..14.02 rows=250 width=33)
> Index Cond: ((test_upper(md5) ~>=~ 'ABAAB'::text) AND
> (test_upper(md5) ~<~ 'ABAAC'::text))
> Filter: (test_upper(md5) ~~ 'ABAAB%'::text)
> (3 rows)
>
>
> So under my assumption that it is the filter that causes the function
> execution, I don't understand
> how a row can satisfy
>
> --which I read as >= 'ABAAB' and < 'ABAAC'
> ((test_upper(md5) ~>=~ 'ABAAB'::text) AND (test_upper(md5) ~<~ 'ABAAC'::text))
>
> and not
>
> (test_upper(md5) ~~ 'ABAAB%'::text)

I don't know, but I suspect it has to do with collations.

Yours,
Laurenz Albe