Alban Hertroys wrote:
Jim Nasby wrote:
Probably a better bet would be going to 8.1 and using constraint
elimination.
Maybe you mean constraint exclusion?
If so, is that going to help excluding partitions (basically the same
thing, it seems) from a query based on an ORDER BY and a LIMIT?
Say we take the query I posted:
"SELECT * FROM mm_posrel ORDER BY number DESC LIMIT 25;"
and the knowledge that this table is inherited by two other tables, with
number being unique across them (though PostgreSQL probably doesn't know
about this).
Can constraint exclusion determine that the last 25 number values do not
occur in some of the tables?
I did some experiments on my PostgreSQL 8.1 server at home (gotta love
UNIX & SSH), with the following setup:
Table "public.object"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------
number | integer | not null default nextval('object_number_seq'::regclass)
title | text | not null
Indexes:
"object_pkey" PRIMARY KEY, btree (number)
Table "public.content"
Column | Type | Modifiers
---------+---------+---------------------------------------------------------
number | integer | not null default
nextval('object_number_seq'::regclass)
title | text | not null
summary | text | not null
body | text | not null
Inherits: object
Table "public.menu_item"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------
number | integer | not null default nextval('object_number_seq'::regclass)
title | text | not null
pos | integer | not null default 1
Inherits: object
I inserted a few records into "object" (30, IIRC) and did:
SET constraint_exclusion=on;
explain analyze select number, title from object order by number desc
limit 10;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=131.34..131.37 rows=10 width=36) (actual
time=0.335..0.358 rows=10 loops=1)
-> Sort (cost=131.34..135.67 rows=1730 width=36) (actual
time=0.331..0.338 rows=10 loops=1)
Sort Key: public."object".number
-> Result (cost=0.00..38.30 rows=1730 width=36) (actual
time=0.097..0.248 rows=30 loops=1)
-> Append (cost=0.00..38.30 rows=1730 width=36)
(actual time=0.091..0.184 rows=30 loops=1)
-> Seq Scan on "object" (cost=0.00..1.30 rows=30
width=12) (actual time=0.090..0.129 rows=30 loops=1)
-> Seq Scan on menu_item "object"
(cost=0.00..21.00 rows=1100 width=36) (actual time=0.001..0.001 rows=0
loops=1)
-> Seq Scan on content "object"
(cost=0.00..16.00 rows=600 width=36) (actual time=0.001..0.001 rows=0
loops=1)
Total runtime: 0.446 ms
(9 rows)
As you can see, it still scans the empty tables menu_item and content.
So I'm afraid this is no solution to our problem... :(
--
Alban Hertroys
alban@xxxxxxxxxxxxxxxxx
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 //
