Re: A slow query - Help please? - Mailing list pgsql-general

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@magproductions.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 //