how are you friend ? i tell you a good news <website:heartytrading.com> best place for online shop thousand of new original products hope you like it take a look , and enjoy here
Monday, June 27, 2011
how are you friend ?
Monday, July 9, 2007
Sunday, March 4, 2007
Reminds me of computer architecture
"He who works with his hands is a laborer. He who works with his hands and his head is a craftsman. He who works with his hands and his head and his heart is an artist."
– St. Francis, religious leader
– St. Francis, religious leader
Saturday, December 23, 2006
Skunk Projects..
"Significant progress doesn't come from the formal planning process of an American corporation. It comes from a couple of guys doing something that hasn't been set down on a list."
– William McGowan, executive
– William McGowan, executive
Sunday, December 17, 2006
Careful with case insensitive searches
I was looking at optimizing a slow login script and narrowed it down to the following slow query:
% select p.party_id from parties p where email ilike ' demoemail@demo.com'
ilike does a case insensitive match on the email column.
Although email is indexed, the case insensitive version is not so
the database therefore has to look at the whole table to
narrow down the possible match.
Next step is to add a funcational index:
% create index parties_lower_email on parties (lower(email));
The query was still slow. (the postgres documentation alluded that ilike would
use the functional index, but when I asked for the query explanation, it didnt').
% explain
% select p.party_id from parties p where email ilike ' demoemail@demo.com' QUERY PLAN ------------------------------------------------------------
Seq Scan on parties p (cost=0.00..20766.76 rows=1 width=29)
Filter: ((email)::text ~~* ' demoemail@demo.com'::text)
(2 rows)
Postgres (7.4) was still doing a sequential scan (10 seconds)
The query became lightning fast when I queried on lower(email) directly:
% select p.party_id from parties p where lower(email) like ' demoemail@demo.com '
And the explain plan showed it was now using the index.
% explain
% select p.party_id from parties p where lower(email) like 'demoemail@demo.com'
QUERY PLAN --------------------------------------------------------------------------------------
Index Scan using parties_lower_email on parties p (cost=0.00..232.62 rows=58 width=29)
Index Cond: (lower((email)::text) = 'demoemail@demo.com '::text)
Filter: (lower((email)::text) ~~ 'demoemail@demo.com::text)
2 lessons:
a) If you are restraining by some variation of a column, make sure you have a functional index
b) Make sure it is used
% select p.party_id from parties p where email ilike ' demoemail@demo.com'
ilike does a case insensitive match on the email column.
Although email is indexed, the case insensitive version is not so
the database therefore has to look at the whole table to
narrow down the possible match.
Next step is to add a funcational index:
% create index parties_lower_email on parties (lower(email));
The query was still slow. (the postgres documentation alluded that ilike would
use the functional index, but when I asked for the query explanation, it didnt').
% explain
% select p.party_id from parties p where email ilike ' demoemail@demo.com' QUERY PLAN ------------------------------------------------------------
Seq Scan on parties p (cost=0.00..20766.76 rows=1 width=29)
Filter: ((email)::text ~~* ' demoemail@demo.com'::text)
(2 rows)
Postgres (7.4) was still doing a sequential scan (10 seconds)
The query became lightning fast when I queried on lower(email) directly:
% select p.party_id from parties p where lower(email) like ' demoemail@demo.com '
And the explain plan showed it was now using the index.
% explain
% select p.party_id from parties p where lower(email) like 'demoemail@demo.com'
QUERY PLAN --------------------------------------------------------------------------------------
Index Scan using parties_lower_email on parties p (cost=0.00..232.62 rows=58 width=29)
Index Cond: (lower((email)::text) = 'demoemail@demo.com '::text)
Filter: (lower((email)::text) ~~ 'demoemail@demo.com::text)
2 lessons:
a) If you are restraining by some variation of a column, make sure you have a functional index
b) Make sure it is used
Subscribe to:
Posts (Atom)
