> Steve, can you try this out on your queries and see if it makes better
> or worse decisions? It seems to fix your initial complaint but I do
> not have a large stock of test cases to try.
>

Wow, this is a remarkable difference. Queries that were taking
minutes to complete are coming up in seconds. Good work, I think this'll
solve my customer's needs for their demo on the 19th

Thank you so much!


Steve


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Steve <cheetah@tanabi.org> writes:
>> Steve, can you try this out on your queries and see if it makes better
>> or worse decisions? It seems to fix your initial complaint but I do
>> not have a large stock of test cases to try.

> Wow, this is a remarkable difference. Queries that were taking
> minutes to complete are coming up in seconds. Good work, I think this'll
> solve my customer's needs for their demo on the 19th

Can you find any cases where it makes a worse choice than before?
Another thing to pay attention to is whether the planning time gets
noticeably worse. If we can't find any cases where it loses badly
on those measures, I'll feel comfortable in applying it...

regards, tom lane

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

>
> Can you find any cases where it makes a worse choice than before?
> Another thing to pay attention to is whether the planning time gets
> noticeably worse. If we can't find any cases where it loses badly
> on those measures, I'll feel comfortable in applying it...
>

I'll see what I can find -- I'll let you know on Monday if I can
find any queries that perform worse. My tests so far have shown
equivalent or better performance so far but I've only done sort of a
survey so far ... I've got plenty of special cases to test that should
put this through the paces.


Steve

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>
> Can you find any cases where it makes a worse choice than before?
> Another thing to pay attention to is whether the planning time gets
> noticeably worse. If we can't find any cases where it loses badly
> on those measures, I'll feel comfortable in applying it...
>

Okay, here's the vedict; all the "extremely slow" queries (i.e.
queries that took more than 30 seconds and upwards of several minutes to
complete) are now running in the realm of reason. In fact, most queries
that took between 1 and 4 minutes are now down to taking about 9 seconds
which is obviously a tremendous improvement.

A few of the queries that were taking 9 seconds or less are
"slightly slower" -- meaning a second or two slower. However most of them
are running at the same speed they were before, or better.

So I'd say as far as I can tell with my application and my
dataset, this change is solid and an obvious improvement.


Talk to you later,

Steve

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Steve wrote:

> >Can you find any cases where it makes a worse choice than before?
> >Another thing to pay attention to is whether the planning time gets
> >noticeably worse. If we can't find any cases where it loses badly
> >on those measures, I'll feel comfortable in applying it...
>
> Okay, here's the vedict; all the "extremely slow" queries (i.e.
> queries that took more than 30 seconds and upwards of several minutes to
> complete) are now running in the realm of reason. In fact, most queries
> that took between 1 and 4 minutes are now down to taking about 9 seconds
> which is obviously a tremendous improvement.
>
> A few of the queries that were taking 9 seconds or less are
> "slightly slower" -- meaning a second or two slower. However most of them
> are running at the same speed they were before, or better.
>
> So I'd say as far as I can tell with my application and my
> dataset, this change is solid and an obvious improvement.

Maybe it would be interesting to see in detail those cases that got a
bit slower, to further tweak the heuristic if necessary. Is the extra
time, time spent in planning or in execution?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

http://www.postgresql.org/docs/faq

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Steve wrote:
>> So I'd say as far as I can tell with my application and my
>> dataset, this change is solid and an obvious improvement.

> Maybe it would be interesting to see in detail those cases that got a
> bit slower, to further tweak the heuristic if necessary. Is the extra
> time, time spent in planning or in execution?

Since there doesn't seem to be vast interest out there in testing this
further, I'm going to go ahead and apply the patch to get it out of my
working directory. We can always tweak it more later if new info
surfaces.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>> Maybe it would be interesting to see in detail those cases that got a
>> bit slower, to further tweak the heuristic if necessary. Is the extra
>> time, time spent in planning or in execution?
>
> Since there doesn't seem to be vast interest out there in testing this
> further, I'm going to go ahead and apply the patch to get it out of my
> working directory. We can always tweak it more later if new info
> surfaces.
>

Doing my routine patching seems to have exploded my mail server,
sorry for not replying sooner!

I don't actually have planning vs. execution time statistics from
the older version for the queries in question -- there were not 'problem
queries' and therefore were never really analyzed. My customer's already
dragging me off to another issue, so I've got to shift gears.

Appreciate all your work -- thanks again!!!


Steve

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