intercepting where clause on a view or other performance tweak - Mailing list pgsql-performance

Hi,

I have a table which contains generated static data (defined below) where the search_key field contains varying length strings.

There are 122,000 rows in the table

When the data is created the search_key field is ordered alphanumerically and assigned a unique order_key value starting at 1.

The table is defined as:

CREATE TABLE stuff

(

  code integer NOT NULL DEFAULT 0,

  search_key character varying(255),

  order_key integer,

  CONSTRAINT "PK_code" PRIMARY KEY (code)

)

CREATE INDEX order_key

  ON stuff

  USING btree

  (order_key);

ALTER TABLE stuff CLUSTER ON order_key;

And a view defined as:

CREATE OR REPLACE VIEW stuff_view AS

select * from stuff

Running the following query takes 56+ ms as it does a seq scan of the whole table:

SELECT CODE FROM stuff

   WHERE SEARCH_KEY LIKE 'AAAAAA%'

Running the following query takes 16+ ms as it does 2 index scans of the order_key index:

   SELECT CODE FROM stuff

   WHERE SEARCH_KEY LIKE 'AAAAAA%'

   and order_key >=

   (

   SELECT order_key FROM stuff

   WHERE SEARCH_KEY LIKE 'AA%'

   order by order_key

   limit 1

   )

   and order_key <

   (

   SELECT order_key FROM stuff

   WHERE SEARCH_KEY LIKE 'AB%'

   order by order_key

   limit 1

   )

Running the following query takes less than a second doing a single index scan:

SELECT CODE FROM stuff

   WHERE SEARCH_KEY  LIKE 'AAAAAA%'

   and order_key >= 14417

   and order_key < 15471

The problem query is always going to be in the first format.

It was my intention to either change the view to intercept the query using a rule and either

add the extra parameters from the second query

OR

                Add a second table which contains the order_key ranges and

add the extra parameters from the third query

Is there an easier way to do this?

As always, thanks for you help…

Regards,

Russell Keane

INPS

Tel:         +44 (0)20 7501 7277

Follow us on twitter | visit www.inps.co.uk