Обсуждение: 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