Re: optimizer question

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Hannu Krosing <hannu(at)tm(dot)ee>, Reinoud van Leeuwen <reinoud(at)xs4all(dot)nl>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: optimizer question
Date: 2001-10-12 17:22:54
Message-ID: 200110121722.f9CHMsx08143@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > Maybe rather
>
> > * Use indexes for min() and max() or convert to "SELECT col FROM tab
> > ORDER BY col DESC USING max_index_op LIMIT 1" if there is an index
> > on tab that uses btree(col max_index_op)
>
> > it seems that in most other cases the rewrite would be either a
> > misoptimisation or plain wrong.
>
> We would clearly need to add information to the system catalogs to allow
> the planner to determine whether a given aggregate matches up to a given
> index opclass. This has been discussed before.
>
> A more interesting question is how to determine whether such a rewrite
> would be a win. That is NOT a foregone conclusion. Consider
>
> SELECT max(col1) FROM tab WHERE col2 BETWEEN 12 AND 42;
>
> Depending on the selectivity of the WHERE condition, we might be far
> better off to scan on a col2 index and use our traditional max()
> code than to scan on a col1 index until we find a row passing the
> WHERE condition. I'm not sure whether the planner currently has
> statistics appropriate for such estimates or not ...

Yes, agreed. This would be just for limited cases. Updated to:

* Use indexes for min() and max() or convert to SELECT col FROM tab ORDER
BY col DESC LIMIT 1 if appropriate index exists and WHERE clause acceptible
^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2001-10-12 17:35:25 Re: PERFORMANCE IMPROVEMENT by mapping WAL FILES
Previous Message Tom Lane 2001-10-12 17:18:49 Re: ecpg - GRANT bug