planning issue

From: Jonathan Vanasco <postgres(at)2xlp(dot)com>
To: pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: planning issue
Date: 2007-03-16 18:31:49
Message-ID: C32FB1DA-F767-49A6-BC7F-6D0474B9E6B8@2xlp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce McAlister 2007-03-16 19:06:57 Re: PostgreSQL 8.2.3 VACUUM Timings/Performance
Previous Message Jonah H. Harris 2007-03-16 18:20:35 Re: [SQL] PostgreSQL to Oracle