intercepting where clause on a view or other performance tweak

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

cid:image001.jpg@01CDBE9B.11D013F0

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

 



Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.uk
The information in this internet email is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is not authorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of INPS or any of its affiliates. If you are not the intended recipient please contact is.helpdesk@xxxxxxxxxx


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux