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))
th
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
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... doe
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
> Subject: Re: [pgadmin-support] [GENERAL] Postgres case
insensitive searches
> From: [email protected]
> Date: Sat, 29 Jun 2013 09:37:51 +0200
> CC: [email protected];
[email protected]; pgadmin-support@postgresq
On 06/29/2013 09:02 AM, bhanu udaya wrote:
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
in
On Jun 29, 2013, at 15:02, bhanu udaya 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
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 w
On Jun 29, 2013, at 3:59, bhanu udaya 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) L
