Thread: 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