Optimizer picks an ineffient plan

[Bupp Phillips]

I have a customer table that has the field CUSTOMER_ID as the primary key
(cust_pkkey), the table has 102,834 records in it.
The following select statement works fine:

select * from customer order by customer_id;
QUERY PLAN:
Index Scan using cust_pkkey on customer (cost=0.00..517 5.17 rows=102834
width=724)
Total runtime: 5999.47 msec

but...

select * from customer order by customer_id, first_name;
QUERY PLAN:
Sort(cost=14202 8.25..142028.25 rows=102834 width=724)
-> Seq Scan on customer (cost=0.00..461 7.34 rows=102834 width=724)
Total runtime: 19999.81 msec
It seems that the optimizer should be able to detect (in this instance at
least) that the first order by field is a primary key and should not
consider the other fields because it's pointless... the resultset will be in
<primary key> order.

NOTE: I'm testing this on Postgresql 7.2 for Windows, so this my have
already been dealt with.
Thanks and keep up the great work!!

[Greg Stark]

"Bupp Phillips" <he***@noname.c om> writes:

but...

select * from customer order by customer_id, first_name;
QUERY PLAN:
Sort(cost=14202 8.25..142028.25 rows=102834 width=724)
-> Seq Scan on customer (cost=0.00..461 7.34 rows=102834 width=724)
Total runtime: 19999.81 msec

Actually in this case the optimizer would likely still use a sequential scan
even if it had an index it thought it could use. If you're going to be reading
the whole table anyways it'll be faster to read it in order than to jump all
around even if you have to sort it.

However you do have a point. In this case I don't think postgres even
considers using the index. Even if it would decide not to use it in this case
there could conceivably be cases where it would want to use it.

However I'm not sure I see a lot of cases where this would come up. Even in
automatically generated code, which is the usual cause of redundant things
like this, I don't think I've seen this particular combination ever come up.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Greg Stark <gs*****@mit.ed u> writes:

"Bupp Phillips" <he***@noname.c om> writes:

select * from customer order by customer_id, first_name;
[ where customer_id is the primary key ]

However you do have a point. In this case I don't think postgres even
considers using the index.
It will not, since the index does not appear to provide the correct sort
order.
However I'm not sure I see a lot of cases where this would come up.

Yes, that's the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I've got grave doubts that it's a win. ISTM such
an optimization penalizes the folk who write their queries well to
benefit those who are careless.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bupp Phillips]

Well, it's unfortunate that you feel that way, because SQL Server handles it
correctly.
"Tom Lane" <tg*@sss.pgh.pa .us> wrote in message
news:43******** *****@sss.pgh.p a.us...

Greg Stark <gs*****@mit.ed u> writes:

"Bupp Phillips" <he***@noname.c om> writes:

select * from customer order by customer_id, first_name;
[ where customer_id is the primary key ]

However you do have a point. In this case I don't think postgres even
considers using the index.

It will not, since the index does not appear to provide the correct sort
order.

However I'm not sure I see a lot of cases where this would come up.

Yes, that's the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I've got grave doubts that it's a win. ISTM such
an optimization penalizes the folk who write their queries well to
benefit those who are careless.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Greg Stark]

Tom Lane <tg*@sss.pgh.pa .us> writes:

However I'm not sure I see a lot of cases where this would come up.

Yes, that's the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I've got grave doubts that it's a win. ISTM such
an optimization penalizes the folk who write their queries well to
benefit those who are careless.

Well I'm sure the same arguments were made 30 years ago about optimizing
compilers. But thankfully the optimizer-geeks won the argument. As a result
these days we can more or less write our code in whatever form is most
readable and flexible. We can spend our time worrying about algorithmic
improvements and design abstraction, and not worry that the extra layer of
abstraction will introduce redundant code or inefficient expressions.

Already today we see database-independent toolkits that write SQL queries for
you. They introduce needless subqueries and other inefficiencies willy-nilly.

I argue that if the performance of the database is important down to a
sub-second constant term per query, then you're talking about an OLTP system
where all the queries ought to be prepared and the plans cached. If you're
talking about a system where queries are constructed ad-hoc for every
execution then you should be talking about a DSS system running batch jobs
where an extra few seconds spent optimizing could save you hours.

All that said I'm not sure the case at hand is a great example. I don't think
it would be a big performance loss, but the added code complexity for nothing
might be annoying. I don't see how even automatically generated code is likely
to generate this situation.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Tom Lane]

Greg Stark <gs*****@mit.ed u> writes:

Tom Lane <tg*@sss.pgh.pa .us> writes:

Yes, that's the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I've got grave doubts that it's a win.

Well I'm sure the same arguments were made 30 years ago about optimizing
compilers. But thankfully the optimizer-geeks won the argument.

Um ... I *am* an optimizer-geek. You can find my name in the credits
for Bliss/11, which was the best optimizing compiler on the planet about
thirty years ago. I stand by my comment that there's a tradeoff between
the potential gain from an optimization and the time spent to find it.

PG is at a disadvantage compared to typical compilation scenarios, in
that a compiler assumes its output will be executed many times, while
SQL queries often are planned and then executed but once. There's been
some talk of working harder when planning a "prepared statement", but
so far I've not seen very many places where I'd really want to alter
the planner's behavior on that basis.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Peter Childs]

On Thu, 4 Sep 2003, Tom Lane wrote:

Greg Stark <gs*****@mit.ed u> writes:

Tom Lane <tg*@sss.pgh.pa .us> writes:

Yes, that's the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I've got grave doubts that it's a win.

Well I'm sure the same arguments were made 30 years ago about optimizing
compilers. But thankfully the optimizer-geeks won the argument.

Um ... I *am* an optimizer-geek. You can find my name in the credits
for Bliss/11, which was the best optimizing compiler on the planet about
thirty years ago. I stand by my comment that there's a tradeoff between
the potential gain from an optimization and the time spent to find it.

PG is at a disadvantage compared to typical compilation scenarios, in
that a compiler assumes its output will be executed many times, while
SQL queries often are planned and then executed but once. There's been
some talk of working harder when planning a "prepared statement", but
so far I've not seen very many places where I'd really want to alter
the planner's behavior on that basis.

An intresting point. Perhaps storing some stats on Views would
help. Maybe adding a cache facility for views would speed some things up.
I don't really see anything against storing stats on "Prepared
Statements" and Views like we do on Tables.
Maybe indexs on View would be useful but keeping them uptodate
would be a hazard.

Peter Childs
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Greg Stark]

Tom Lane <tg*@sss.pgh.pa .us> writes:

Um ... I *am* an optimizer-geek.
That explains so much :)
I stand by my comment that there's a tradeoff between the potential gain
from an optimization and the time spent to find it.
Well there are always tradoffs in engineering. I'm just trying to push a
little bit in one direction and make you rethink your assumptions.

In the past postgres users were largely OLTP systems (largely web sites)
running ad-hoc queries that are parsed and executed every time and interpolate
parameters into the query string. That's crap. With the new FE binary
protocol, and a bit of a push to the driver writers a good high performance
(and secure) system ought to be able to run entirely prepared queries that are
prepared once per backend process and executed thousands of times.
PG is at a disadvantage compared to typical compilation scenarios, in
that a compiler assumes its output will be executed many times, while
SQL queries often are planned and then executed but once. There's been
some talk of working harder when planning a "prepared statement", but
so far I've not seen very many places where I'd really want to alter
the planner's behavior on that basis.

I think that's backwards actually. The queries where you would want to work
super extra hard, spending a few seconds or even minutes checking possible
plans are the ones that will run for hours. Those are more likely to be DSS
queries that are unprepared ad-hoc queries that will be executed only once.

For OLTP queries I think postgres can afford to not worry about small
(subsecond) constant-time optimizations even if they're unlikely to return big
benefits because OLTP systems should be running entirely prepared queries.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Stephan Szabo]

On Wed, 3 Sep 2003, Bupp Phillips wrote:

Well, it's unfortunate that you feel that way, because SQL Server handles it
correctly.
For some definition of correctly. If you're in a system which gets
penalized .001 seconds for each query planning that uses a multi-column
order by and you do 100 million of them that this doesn't apply to, and
one that it does which save you 30 seconds, is that correct?

"Tom Lane" <tg*@sss.pgh.pa .us> wrote in message
news:43******** *****@sss.pgh.p a.us...

Greg Stark <gs*****@mit.ed u> writes:

"Bupp Phillips" <he***@noname.c om> writes:
> select * from customer order by customer_id, first_name;
> [ where customer_id is the primary key ]

However you do have a point. In this case I don't think postgres even
considers using the index.

It will not, since the index does not appear to provide the correct sort
order.

However I'm not sure I see a lot of cases where this would come up.

Yes, that's the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I've got grave doubts that it's a win. ISTM such
an optimization penalizes the folk who write their queries well to
benefit those who are careless.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Relaxin]

What the heck are you talking about?

SQLServer returns the query immediately (milliseconds) where as PGSQL
returns 6 seconds!!
So, yes I would say that SQLServer did it correctly in this situation. The
optimizer did what it was suppose to do...find the quickest way to get me
the data.
Now if you guys here a PGSQL don't believe it's necessary to change the
optimizer for this purpose, that's fine, but don't start putting other DBMS
down because they DO handle this situation.
"Stephan Szabo" <ss****@megazon e.bigpanda.com> wrote in message
news:20******** *************** ***@megazone.bi gpanda.com...

On Wed, 3 Sep 2003, Bupp Phillips wrote:

Well, it's unfortunate that you feel that way, because SQL Server handles it correctly.

For some definition of correctly. If you're in a system which gets
penalized .001 seconds for each query planning that uses a multi-column
order by and you do 100 million of them that this doesn't apply to, and
one that it does which save you 30 seconds, is that correct?

"Tom Lane" <tg*@sss.pgh.pa .us> wrote in message
news:43******** *****@sss.pgh.p a.us...

Greg Stark <gs*****@mit.ed u> writes:
> "Bupp Phillips" <he***@noname.c om> writes:
>> select * from customer order by customer_id, first_name;
>> [ where customer_id is the primary key ]

> However you do have a point. In this case I don't think postgres even > considers using the index.

It will not, since the index does not appear to provide the correct sort order.

> However I'm not sure I see a lot of cases where this would come up.

Yes, that's the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I've got grave doubts that it's a win. ISTM such
an optimization penalizes the folk who write their queries well to
benefit those who are careless.

regards, tom lane

---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match