Re: A slow query - Help please?

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(at)magproductions(dot)nl

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 //