pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedintercepting where clause on a view or other performance tweak
5+ messages / 2 participants
[nested] [flat]
* intercepting where clause on a view or other performance tweak
@ 2012-11-16 14:28 Russell Keane <Russell.Keane@inps.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Russell Keane @ 2012-11-16 14:28 UTC (permalink / raw)
To: 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
[cid:image001.jpg@01CDC3FE.8C3BC810]
Follow us<https://twitter.com/INPSnews; on twitter | visit www.inps.co.uk<http://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@inps.co.uk
Attachments:
image001.jpg (image/jpeg, 9.7K) download
view image
* Re: intercepting where clause on a view or other performance tweak
@ 2012-11-16 15:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Russell Keane <Russell.Keane@inps.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2012-11-16 15:05 UTC (permalink / raw)
To: Russell Keane <Russell.Keane@inps.co.uk>; +Cc: pgsql-performance
Russell Keane <Russell.Keane@inps.co.uk> writes:
> 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%'
Why don't you create an index on search_key, and forget all these other
machinations? (If your locale isn't C you'll need to use a
varchar_pattern_ops index.)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: intercepting where clause on a view or other performance tweak
@ 2012-11-16 15:17 Russell Keane <Russell.Keane@inps.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Russell Keane @ 2012-11-16 15:17 UTC (permalink / raw)
To: pgsql-performance
Sorry, I should've added that in the original description.
I have an index on search_key and it's never used.
If it makes any difference, the table is about 9MB and the index on that field alone is 3MB.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 November 2012 15:05
To: Russell Keane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] intercepting where clause on a view or other performance tweak
Russell Keane <Russell.Keane@inps.co.uk> writes:
> 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%'
Why don't you create an index on search_key, and forget all these other machinations? (If your locale isn't C you'll need to use a varchar_pattern_ops index.)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: intercepting where clause on a view or other performance tweak
@ 2012-11-16 15:22 Russell Keane <Russell.Keane@inps.co.uk>
parent: Russell Keane <Russell.Keane@inps.co.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Russell Keane @ 2012-11-16 15:22 UTC (permalink / raw)
To: pgsql-performance
I should've also mentioned that we're using PG 9.0.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Russell Keane
Sent: 16 November 2012 15:18
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] intercepting where clause on a view or other performance tweak
Sorry, I should've added that in the original description.
I have an index on search_key and it's never used.
If it makes any difference, the table is about 9MB and the index on that field alone is 3MB.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 November 2012 15:05
To: Russell Keane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] intercepting where clause on a view or other performance tweak
Russell Keane <Russell.Keane@inps.co.uk> writes:
> 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%'
Why don't you create an index on search_key, and forget all these other machinations? (If your locale isn't C you'll need to use a varchar_pattern_ops index.)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: intercepting where clause on a view or other performance tweak
@ 2012-11-16 15:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Russell Keane <Russell.Keane@inps.co.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2012-11-16 15:41 UTC (permalink / raw)
To: Russell Keane <Russell.Keane@inps.co.uk>; +Cc: pgsql-performance
Russell Keane <Russell.Keane@inps.co.uk> writes:
> Sorry, I should've added that in the original description.
> I have an index on search_key and it's never used.
Did you pay attention to the point about the nondefault operator class?
If the LIKE pattern is left-anchored and as selective as your example
implies, the planner certainly ought to try to use a compatible index.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-11-16 15:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-16 14:28 intercepting where clause on a view or other performance tweak Russell Keane <Russell.Keane@inps.co.uk>
2012-11-16 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-11-16 15:17 ` Russell Keane <Russell.Keane@inps.co.uk>
2012-11-16 15:22 ` Russell Keane <Russell.Keane@inps.co.uk>
2012-11-16 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox