planning issue
if you have time, could you offer advice on this:
i'm doing a database cleanup right now -- 1.4M records -- and each
query is taking 1 second
i can't really wait 2 weeks for this to finish , so I'm hoping that
someone will be able to help out
the issue is that the planner keeps doing a sequential scan, despite
the fact that the requesite columns are indexed.
hoping someone may be able to offer advice:.
SELECT
*
FROM
table_a
WHERE
id != 10001
AND
(
( field_1 ilike '123' )
OR
( field_2 ilike 'abc' )
)
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------
Seq Scan on table_a (cost=0.00..22779.68 rows=1 width=346)
Filter: ((id <> 10001) AND (((field_1)::text ~~* '123'::text) OR
((field_2)::text ~~* 'abc'::text)))
however, i have the following indexes:
"table_a__pkey" PRIMARY KEY, btree (id)
"table_a__idx__field_1" btree (field_1)
"table_a__idx__field_2" btree (field_2)
can anyone offer advice to help me use the indexes on this ?
// Jonathan Vanasco
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------Seq Scan on table_a (cost=0.00..22779.68 rows=1 width=346)
Filter: ((id <> 10001) AND (((field_1)::text ~~* '123'::text) OR
((field_2)::text ~~* 'abc'::text)))however, i have the following indexes:
"table_a__pkey" PRIMARY KEY, btree (id)
"table_a__idx__field_1" btree (field_1)
"table_a__idx__field_2" btree (field_2)can anyone offer advice to help me use the indexes on this ?
create a function lower index and instead of calling ilike call ~
lower('123')
Joshua D. Drake
// Jonathan Vanasco
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Fri, 2007-03-16 at 12:17 -0700, Joshua D. Drake wrote:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------Seq Scan on table_a (cost=0.00..22779.68 rows=1 width=346)
Filter: ((id <> 10001) AND (((field_1)::text ~~* '123'::text) OR
((field_2)::text ~~* 'abc'::text)))however, i have the following indexes:
"table_a__pkey" PRIMARY KEY, btree (id)
"table_a__idx__field_1" btree (field_1)
"table_a__idx__field_2" btree (field_2)can anyone offer advice to help me use the indexes on this ?
create a function lower index and instead of calling ilike call ~
lower('123')
To clarify a little:
CREATE INDEX table_a_lower_field_1_idx on table_a ((lower(field_1)));
CREATE INDEX table_a_lower_field_2_idx on table_a ((lower(field_2)));
SELECT
*
FROM
table_a
WHERE
id != 10001
AND
(
( lower(field_1) = '123' )
OR
( lower(field_2) = 'abc' )
)
That should be able to use your indexes correctly.
Regards,
Jeff Davis
On Mar 16, 2007, at 3:48 PM, Jeff Davis wrote:
To clarify a little:
No clarifcation needed. Joshua Drake's suggestion made perfect sense
and I was able to implement in 2 seconds.
works like a charm!
ETA 2 weeks -> 30mins
Thanks to all.
// Jonathan Vanasco
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
Jonathan Vanasco wrote:
hoping someone may be able to offer advice:.
SELECT
*
FROM
table_a
WHERE
id != 10001
AND
(
( field_1 ilike '123' )
OR
( field_2 ilike 'abc' )
)
You seem to use that ilike expression merely as a case-insensitive
equals. May as well use that in combination with indices on
lower(field_[12]). It's probably faster than like or a regex match.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
create a function lower index and instead of calling ilike call ~
lower('123')To clarify a little:
CREATE INDEX table_a_lower_field_1_idx on table_a ((lower(field_1)));
CREATE INDEX table_a_lower_field_2_idx on table_a ((lower(field_2)));
SELECT
*
FROM
table_a
WHERE
id != 10001
AND
(
( lower(field_1) = '123' )
OR
( lower(field_2) = 'abc' )
)
To put my own two cents in, I always try to make sure I use lower()
in the query on everything I'm comparing, as Josh originally
suggested, so I would do this:
lower(field_2) = lower('abc')
This ensures that both sides of the comparison are being downcased
the same way - otherwise there might be a discrepancy due to
collation differences, etc., between the client and the server sides.
This seems silly in this example, but I think it's a good habit.
- John Burger
MITRE