pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: bad estimates
25+ messages / 11 participants
[nested] [flat]
* Re: bad estimates
@ 2003-08-29 03:46 Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 25+ messages in thread
From: Ken Geis @ 2003-08-29 03:46 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Bruno Wolff III wrote:
> On Thu, Aug 28, 2003 at 20:00:32 -0700,
> Ken Geis <kgeis@speakeasy.org> wrote:
>
>>Bruno Wolff III wrote:
>>
>>>>Not according to the optimizer! Plus, this is not guaranteed to return
>>>>the correct results.
>>>
>>>For it to be fast you need an index on (stock_id, price_date) so that
>>>you can use an index scan.
>>
>>I already said that such an index existed. In fact, it is the primary
>>key of the table. And yes, I *am* analyzed!
>
>
> Your original example didn't actually match that of the table you are showing
> examples from. In that example the second half of the primary key was the
> date not the end of the day price. If this is the case for the real table,
> then that is the reason the distinct on doesn't help.
I had obfuscated the table in the example and forgot to do the same with
the query. Serves me right for thinking I care about that.
A big problem is that the values I am working with are *only* the
primary key and the optimizer is choosing a table scan over an index
scan. That is why I titled the email "bad estimates." The table has
(stock_id, price_date) as the primary key, and a bunch of other columns.
What I *really* want to do efficiently is
select stock_id, min(price_date), max(price_date)
from day_ends
group by stock_id;
It is not the table or the query that is wrong. It is either the db
parameters or the optimizer itself.
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 04:05 ` Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 16:06 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
0 siblings, 2 replies; 25+ messages in thread
From: Bruno Wolff III @ 2003-08-29 04:05 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: pgsql-performance
On Thu, Aug 28, 2003 at 20:46:00 -0700,
Ken Geis <kgeis@speakeasy.org> wrote:
>
> A big problem is that the values I am working with are *only* the
> primary key and the optimizer is choosing a table scan over an index
> scan. That is why I titled the email "bad estimates." The table has
> (stock_id, price_date) as the primary key, and a bunch of other columns.
> What I *really* want to do efficiently is
>
> select stock_id, min(price_date), max(price_date)
> from day_ends
> group by stock_id;
>
> It is not the table or the query that is wrong. It is either the db
> parameters or the optimizer itself.
If you want both the max and the min, then things are going to be a bit
more work. You are either going to want to do two separate selects
or join two selects or use subselects. If there aren't enough prices
per stock, the sequential scan might be fastest since you only need to
go through the table once and don't have to hit the index blocks.
It is still odd that you didn't get a big speed up for just the min though.
You example did have the stock id and the date as the primary key which
would make sense since the stock id and stock price on a day wouldn't
be guarenteed to be unique. Are you absolutely sure you have a combined
key on the stock id and the stock price?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
@ 2003-08-29 04:09 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 25+ messages in thread
From: Ken Geis @ 2003-08-29 04:09 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Bruno Wolff III wrote:
> On Thu, Aug 28, 2003 at 20:46:00 -0700,
> Ken Geis <kgeis@speakeasy.org> wrote:
>>It is not the table or the query that is wrong. It is either the db
>>parameters or the optimizer itself.
...
>
> It is still odd that you didn't get a big speed up for just the min though.
> You example did have the stock id and the date as the primary key which
> would make sense since the stock id and stock price on a day wouldn't
> be guarenteed to be unique. Are you absolutely sure you have a combined
> key on the stock id and the stock price?
I am positive! I can send a log if you want, but I won't post it to the
list.
The arity on the data is roughly 1500 price_dates per stock_id.
I was able to get the query to return in a reasonable amount of time
(still ~3 minutes) by forcing a nested loop path using SQL functions
instead of min and max.
I'm going to run comparisons on 7.3.3 and 7.4-beta2. I'll also look
into the optimizer source to try to figure out why it thinks scanning
this index is so expensive.
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 04:24 ` Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:01 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 07:57 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
0 siblings, 2 replies; 25+ messages in thread
From: Bruno Wolff III @ 2003-08-29 04:24 UTC (permalink / raw)
To: kgeis@alum.calberkeley.org; +Cc: pgsql-performance
On Thu, Aug 28, 2003 at 21:09:00 -0700,
Ken Geis <kgeis@speakeasy.org> wrote:
> Bruno Wolff III wrote:
>
> I am positive! I can send a log if you want, but I won't post it to the
> list.
Can you do a \d on the real table or is that too sensitive?
It still doesn't make sense that you have a primary key that
is a stock and its price. What happens when the stock has the
same price on two different dates? And I doubt that you are looking
for the minimum and maximum dates for which you have price data.
So it is hard to believe that the index for your primary key is the
one you need for your query.
> The arity on the data is roughly 1500 price_dates per stock_id.
Two index scans (one for min values and another for max values)
should be better than one sequential scan under those conditions.
I am calling it quits for tonight, but will check back tomorrow
to see how things turned out.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
@ 2003-08-29 07:01 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:17 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
1 sibling, 1 reply; 25+ messages in thread
From: Ken Geis @ 2003-08-29 07:01 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Bruno Wolff III wrote:
> Can you do a \d on the real table or is that too sensitive?
It was silly of me to think of this as particularly sensitive.
stocks=> \d day_ends
Table "public.day_ends"
Column | Type | Modifiers
------------+--------------+-----------
stock_id | integer | not null
price_date | date | not null
open | numeric(9,4) |
high | numeric(9,4) |
low | numeric(9,4) |
close | numeric(9,4) |
volume | integer |
Indexes: day_ends_pkey primary key btree (stock_id, price_date)
Triggers: RI_ConstraintTrigger_16558399
> It still doesn't make sense that you have a primary key that
> is a stock and its price. What happens when the stock has the
> same price on two different dates? And I doubt that you are looking
> for the minimum and maximum dates for which you have price data.
> So it is hard to believe that the index for your primary key is the
> one you need for your query.
I can see the naming being confusing. I used "price_date" because, of
course, "date" is not a legal name. "day_ends" is a horrible name for
the table; "daily_bars" would probably be better. I *am* looking for
the mininum and maximum dates for which I have price data. I'm running
this query to build a chart so I can see visually where the majority of
my data begins to use as the start of a window for analysis.
When run on 7.3.3, forcing an index scan by setting
enable_seqscan=false, the query took 55 minutes to run. The index is
about 660M in size, and the table is 1G. As I mentioned before, with
table scans enabled, it bombs, running out of temporary space.
Hey Bruno, thanks for your attention here. I'm not a newbie, but I've
never really had performance issues with pgsql before. And I've been
running this database for a couple of years now, but I haven't run these
queries against it.
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:01 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 08:17 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 12:52 ` Re: bad estimates Jeff <threshar@torgo.978.org>
0 siblings, 1 reply; 25+ messages in thread
From: Ken Geis @ 2003-08-29 08:17 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Ken Geis wrote:
> When run on 7.3.3, forcing an index scan by setting
> enable_seqscan=false, the query took 55 minutes to run. The index is
> about 660M in size, and the table is 1G. As I mentioned before, with
> table scans enabled, it bombs, running out of temporary space.
Man, I should wait a while before I send mails, because I keep having
more to say!
Some good news here. Doing the same as above on 7.4beta2 took 29
minutes. Now, the 7.3.3 was on reiser and 7.4 on ext2, so take that as
you will. 7.4's index selectivity estimate seems much better; 7.3.3's
anticipated rows was ten times the actual; 7.4's is one half of the actual.
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:01 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:17 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 12:52 ` Jeff <threshar@torgo.978.org>
0 siblings, 0 replies; 25+ messages in thread
From: Jeff @ 2003-08-29 12:52 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-performance
On Fri, 29 Aug 2003, Ken Geis wrote:
> Some good news here. Doing the same as above on 7.4beta2 took 29
> minutes. Now, the 7.3.3 was on reiser and 7.4 on ext2, so take that as
> you will. 7.4's index selectivity estimate seems much better; 7.3.3's
> anticipated rows was ten times the actual; 7.4's is one half of the actual.
>
Min() & Max() unfortunatly suck on PG. It will be that way for a while
perhaps at some point someone will make a "special" case and convince
-HACKERS it is a Good Thing(tm) (Like select count(*) from table being
'cached' - a lot of people probably get bad first impressions because of
that)
Would it be possible ot rewrite your queries replacing min/max with a
select stock_id from bigtable where blah = blorch order by stock_id
(desc|asc) limit 1? because that would enable PG to use an index and
magically "go fast". You may need a subselect..
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
@ 2003-08-29 07:57 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:10 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-30 02:55 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
1 sibling, 2 replies; 25+ messages in thread
From: Ken Geis @ 2003-08-29 07:57 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Sorry, all, to wipe out the context, but it was getting a little long.
Bruno Wolff III wrote:
> I am calling it quits for tonight, but will check back tomorrow
> to see how things turned out.
I went through the code (7.4 beta2) that estimates the cost of an index
scan path. What I need to be sure of is that when running a query in
pgsql that uses only the columns that are in an index, the underlying
table need not be accessed. I know that Oracle does this.
The cost_index function is assuming that after finding an entry in the
index it will be looking it up in the underlying table. That table is
not well correlated to the index, so it is assuming (in the worst case)
a random page lookup for each of 17 million records! In my case, if the
underlying table is indeed not touched, the estimated cost is 1000 times
the real cost.
63388.624000 to scan the index
67406506.915595 to scan the index and load a random page for each entry
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:57 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 08:10 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:15 ` Re: bad estimates Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-29 15:02 ` Re: bad estimates Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 2 replies; 25+ messages in thread
From: Ken Geis @ 2003-08-29 08:10 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Ken Geis wrote:
> I went through the code (7.4 beta2) that estimates the cost of an index
> scan path. What I need to be sure of is that when running a query in
> pgsql that uses only the columns that are in an index, the underlying
> table need not be accessed. I know that Oracle does this.
Thinking about it some more, it's obvious to me that a pgsql index scan
must be accessing the underlying table even though all of the
information needed is in the index itself. A linear scan of a 660M file
should not take 55 minutes. I could confirm this with stats, but
someone out there probably already knows the answer here.
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:57 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:10 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 08:15 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 25+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-29 08:15 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
> > I went through the code (7.4 beta2) that estimates the cost of an index
> > scan path. What I need to be sure of is that when running a query in
> > pgsql that uses only the columns that are in an index, the underlying
> > table need not be accessed. I know that Oracle does this.
PostgreSQL absolutely does not do this. It is also not possible to do this
due to MVCC.
Chris
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:57 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:10 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 15:02 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 25+ messages in thread
From: Stephan Szabo @ 2003-08-29 15:02 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-performance
On Fri, 29 Aug 2003, Ken Geis wrote:
> Ken Geis wrote:
> > I went through the code (7.4 beta2) that estimates the cost of an index
> > scan path. What I need to be sure of is that when running a query in
> > pgsql that uses only the columns that are in an index, the underlying
> > table need not be accessed. I know that Oracle does this.
>
> Thinking about it some more, it's obvious to me that a pgsql index scan
> must be accessing the underlying table even though all of the
> information needed is in the index itself. A linear scan of a 660M file
> should not take 55 minutes. I could confirm this with stats, but
> someone out there probably already knows the answer here.
Unfortunately not all the information needed is in the index. You can't
tell from the index alone currently whether or not the row is visible to
you. Adding said information would be possible but there are downsides to
that as well (there are some past discussions on the topic, but I'm too
lazy to look them up to give a link, check the archives ;) ).
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:57 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-30 02:55 ` Bruno Wolff III <bruno@wolff.to>
2003-08-30 05:05 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-30 07:38 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
1 sibling, 2 replies; 25+ messages in thread
From: Bruno Wolff III @ 2003-08-30 02:55 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: pgsql-performance
I haven't come up with any great ideas for this one. It might be interesting
to compare the explain analyze output from the distinct on query with
and without seqscans enabled.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:57 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-30 02:55 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
@ 2003-08-30 05:05 ` Ken Geis <kgeis@speakeasy.org>
2003-08-30 14:35 ` Re: bad estimates Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 25+ messages in thread
From: Ken Geis @ 2003-08-30 05:05 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Bruno Wolff III wrote:
> I haven't come up with any great ideas for this one. It might be interesting
> to compare the explain analyze output from the distinct on query with
> and without seqscans enabled.
Can't do that comparison. Remember, with seqscan it fails. (Oh, and
that nested loops solution I thought was fast actually took 31 minutes
versus 29 for index scan in 7.4b2.)
I ran another query across the same data:
select price_date, count(*) from day_ends group by price_date;
It used a table scan and hashed aggregates, and it ran in 5.5 minutes.
Considering that, pgsql should be able to do the query that I had been
running in a little more time than that. So...
From what I've learned, we want to convince the optimizer to use a
table scan; that's a good thing. I want it to use hashed aggregates,
but I can't convince it to (unless maybe I removed all of the
statistics.) To use group aggregates, it first sorts the results of the
table scan (all 17 million rows!) There ought to be some way to tell
pgsql not to do sorts above a certain size. In this case, if I set
enable_sort=false, it goes back to the index scan. If I then set
enable_indexscan=false, it goes back to sorting.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:57 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-30 02:55 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-30 05:05 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-30 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 25+ messages in thread
From: Tom Lane @ 2003-08-30 14:35 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-performance
Ken Geis <kgeis@speakeasy.org> writes:
> From what I've learned, we want to convince the optimizer to use a
> table scan; that's a good thing. I want it to use hashed aggregates,
> but I can't convince it to (unless maybe I removed all of the
> statistics.)
You probably just need to increase sort_mem. Multiple aggregates take
more RAM to process in a hashtable style ...
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:57 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-30 02:55 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
@ 2003-08-30 07:38 ` Ken Geis <kgeis@speakeasy.org>
1 sibling, 0 replies; 25+ messages in thread
From: Ken Geis @ 2003-08-30 07:38 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Bruno Wolff III wrote:
> I haven't come up with any great ideas for this one. It might be interesting
> to compare the explain analyze output from the distinct on query with
> and without seqscans enabled.
After digging through planner code, I found that bumping up the sort_mem
will make the planner prefer a full table scan and hashed aggregation.
The sort memory is where the hash table is stored. In the end, the
query runs in 4.5 minutes, which is reasonable.
I had planned to try Manfred's index correlation patch to see if it
would give better estimates for an index scan. The index scan method
took maybe 6.5x as long, but the estimate was that it would take 1400x
as long. I think instead of trying out his patch I might actually work
on my application!
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
@ 2003-08-29 16:06 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 16:36 ` Re: bad estimates Sean Chittenden <sean@chittenden.org>
1 sibling, 1 reply; 25+ messages in thread
From: Ken Geis @ 2003-08-29 16:06 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Bruno Wolff III wrote:
> If you want both the max and the min, then things are going to be a bit
> more work. You are either going to want to do two separate selects
> or join two selects or use subselects. If there aren't enough prices
> per stock, the sequential scan might be fastest since you only need to
> go through the table once and don't have to hit the index blocks.
>
> It is still odd that you didn't get a big speed up for just the min though.
I found I'm suffering from an effect detailed in a previous thread titled
Does "correlation" mislead the optimizer on large tables?
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 16:06 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 16:36 ` Sean Chittenden <sean@chittenden.org>
2003-08-29 16:56 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
0 siblings, 1 reply; 25+ messages in thread
From: Sean Chittenden @ 2003-08-29 16:36 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-performance
> >If you want both the max and the min, then things are going to be a
> >bit more work. You are either going to want to do two separate
> >selects or join two selects or use subselects. If there aren't
> >enough prices per stock, the sequential scan might be fastest since
> >you only need to go through the table once and don't have to hit
> >the index blocks.
> >
> >It is still odd that you didn't get a big speed up for just the min though.
>
> I found I'm suffering from an effect detailed in a previous thread titled
>
> Does "correlation" mislead the optimizer on large tables?
I don't know about large tables, but this is a big problem and
something I'm going to spend some time validating later today. I
think Manfred's patch is pretty good and certainly better than where
we are but I haven't used it yet to see if it's the magic ticket for
many of these index problems.
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 16:06 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 16:36 ` Re: bad estimates Sean Chittenden <sean@chittenden.org>
@ 2003-08-29 16:56 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 17:03 ` Re: bad estimates Sean Chittenden <sean@chittenden.org>
0 siblings, 1 reply; 25+ messages in thread
From: Ken Geis @ 2003-08-29 16:56 UTC (permalink / raw)
To: Sean Chittenden <sean@chittenden.org>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-performance
Sean Chittenden wrote:
>>I found I'm suffering from an effect detailed in a previous thread titled
>>
>> Does "correlation" mislead the optimizer on large tables?
>
>
> I don't know about large tables, but this is a big problem and
> something I'm going to spend some time validating later today. I
> think Manfred's patch is pretty good and certainly better than where
> we are but I haven't used it yet to see if it's the magic ticket for
> many of these index problems.
I had to dig through a lot of archives to find this. Is this the patch,
from last October?
http://members.aon.at/pivot/pg/16-correlation.diff
If so, I'll try it out and report my results.
Ken
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: bad estimates
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Re: bad estimates Bruno Wolff III <bruno@wolff.to>
2003-08-29 16:06 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 16:36 ` Re: bad estimates Sean Chittenden <sean@chittenden.org>
2003-08-29 16:56 ` Re: bad estimates Ken Geis <kgeis@speakeasy.org>
@ 2003-08-29 17:03 ` Sean Chittenden <sean@chittenden.org>
0 siblings, 0 replies; 25+ messages in thread
From: Sean Chittenden @ 2003-08-29 17:03 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-performance
> >>I found I'm suffering from an effect detailed in a previous thread titled
> >>
> >> Does "correlation" mislead the optimizer on large tables?
> >
> >
> >I don't know about large tables, but this is a big problem and
> >something I'm going to spend some time validating later today. I
> >think Manfred's patch is pretty good and certainly better than where
> >we are but I haven't used it yet to see if it's the magic ticket for
> >many of these index problems.
>
> I had to dig through a lot of archives to find this. Is this the patch,
> from last October?
>
> http://members.aon.at/pivot/pg/16-correlation.diff
>
> If so, I'll try it out and report my results.
Same guy, but that patch is pretty out of date and has been replaced
by some newer work that's much better.
From: Manfred Koizar <mkoi-pg@aon.at>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Correlation in cost_index()
Date: Wed, 20 Aug 2003 19:57:12 +0200
Message-ID: <lo97kvkmjatb0ain1e7ad69ccslripcafv@4ax.com>
and
From: Manfred Koizar <mkoi-pg@aon.at>
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] Again on index correlation
Date: Wed, 20 Aug 2003 21:21:14 +0200
Message-ID: <dhd7kvs4niqijnerr9mi38oeih1o7j2s28@4ax.com>
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 25+ messages in thread
* Bad estimates
@ 2017-11-22 14:29 =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
2017-11-22 14:49 ` Re: Bad estimates Don Seiler <don@seiler.us>
2017-11-22 15:01 ` Re: Bad estimates Tom Lane <tgl@sss.pgh.pa.us>
2017-11-22 15:09 ` Re: Bad estimates Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 25+ messages in thread
From: Artur Zając @ 2017-11-22 14:29 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi,
We have table created like this:
CREATE TABLE xyz AS SELECT generate_series(1,10000000,1) AS gs;
Now:
db=# explain analyze select * from xyz where gs&1=1;
QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------
Seq Scan on xyz (cost=0.00..260815.38 rows=68920 width=4) (actual
time=0.044..2959.728 rows=5000000 loops=1)
Filter: ((gs & 1) = 1)
Rows Removed by Filter: 5000000
Planning time: 0.133 ms
Execution time: 3340.886 ms
(5 rows)
And after adding additional clause to WHERE:
db=# explain analyze select * from xyz where gs&1=1 and gs&2=2;
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------
Seq Scan on xyz (cost=0.00..329735.50 rows=345 width=4) (actual
time=0.045..3010.430 rows=2500000 loops=1)
Filter: (((gs & 1) = 1) AND ((gs & 2) = 2))
Rows Removed by Filter: 7500000
Planning time: 0.106 ms
Execution time: 3176.355 ms
(5 rows)
And one more clause:
newrr=# explain analyze select * from xyz where gs&1=1 and gs&2=2 and
gs&4=4;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------
Seq Scan on xyz (cost=0.00..398655.62 rows=2 width=4) (actual
time=0.052..3329.422 rows=1250000 loops=1)
Filter: (((gs & 1) = 1) AND ((gs & 2) = 2) AND ((gs & 4) = 4))
Rows Removed by Filter: 8750000
Planning time: 0.119 ms
Execution time: 3415.839 ms
(5 rows)
As we can see estimates differs significally from the actual records count -
only three clauses are reducing estimated number of records from 10000000 to
2.
I noticed that each additional clause reduces the number about 200 times and
define DEFAULT_NUM_DISTINCT is responsible for this behaviur.
I think that this variable should be lower or maybe estimation using
DEFAULT_NUM_DISTTINCT should be done once per table.
Artur Zajac
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Bad estimates
2017-11-22 14:29 Bad estimates =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
@ 2017-11-22 14:49 ` Don Seiler <don@seiler.us>
2017-11-22 15:05 ` RE: Bad estimates Alex Ignatov <a.ignatov@postgrespro.ru>
2 siblings, 1 reply; 25+ messages in thread
From: Don Seiler @ 2017-11-22 14:49 UTC (permalink / raw)
To: Artur Zając <azajac@ang.com.pl>; +Cc: pgsql-performance@lists.postgresql.org
I'm assuming you never analyzed the table after creation & data load? What
does this show you:
select * from pg_stat_all_tables where relname='xyz';
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 25+ messages in thread
* RE: Bad estimates
2017-11-22 14:29 Bad estimates =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
2017-11-22 14:49 ` Re: Bad estimates Don Seiler <don@seiler.us>
@ 2017-11-22 15:05 ` Alex Ignatov <a.ignatov@postgrespro.ru>
0 siblings, 0 replies; 25+ messages in thread
From: Alex Ignatov @ 2017-11-22 15:05 UTC (permalink / raw)
To: 'Don Seiler' <don@seiler.us>; 'Artur Zając' <azajac@ang.com.pl>; +Cc: pgsql-performance@lists.postgresql.org
It doesn’t help in this case.
--
Alex Ignatov
Postgres Professional: <http://www.postgrespro.com; http://www.postgrespro.com
The Russian Postgres Company
From: Don Seiler [mailto:don@seiler.us]
Sent: Wednesday, November 22, 2017 5:49 PM
To: Artur Zając <azajac@ang.com.pl>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates
I'm assuming you never analyzed the table after creation & data load? What does this show you:
select * from pg_stat_all_tables where relname='xyz';
Don.
--
Don Seiler
www.seiler.us <http://www.seiler.us;
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Bad estimates
2017-11-22 14:29 Bad estimates =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
@ 2017-11-22 15:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-11-22 16:52 ` RE: Bad estimates =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
2 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2017-11-22 15:01 UTC (permalink / raw)
To: Artur Zając <azajac@ang.com.pl>; +Cc: pgsql-performance@lists.postgresql.org
=?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl> writes:
[ poor estimates for WHERE clauses like "(gs & 1) = 1" ]
Don't hold your breath waiting for that to get better on its own.
You need to work with the planner, not expect it to perform magic.
It has no stats that would help it discover what the behavior of
that sort of WHERE clause is; nor is there a good reason for it
to think that the selectivity of such a clause is only 0.5 rather
than something more in line with the usual behavior of an equality
constraint on an integer value.
One way you could attack the problem, if you're wedded to this data
representation, is to create expression indexes on the terms "(gs & x)"
for all the values of x you use. Not only would that result in better
estimates (after an ANALYZE) but it would also open the door to satisfying
this type of query through an index search. A downside is that updating
all those indexes could make DML on the table pretty expensive.
If you're not wedded to this data representation, consider replacing that
integer flags column with a bunch of boolean columns. You might or might
not want indexes on the booleans, but in any case ANALYZE would create
stats that would allow decent estimates for "WHERE boolval".
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* RE: Bad estimates
2017-11-22 14:29 Bad estimates =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
2017-11-22 15:01 ` Re: Bad estimates Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-11-22 16:52 ` =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
0 siblings, 0 replies; 25+ messages in thread
From: Artur Zając @ 2017-11-22 16:52 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Thank you for your response,
Clause used by me is not important (I used binary & operator only for
example), I tried to show some kind of problems.
Now I did another test:
alter table xyz add x int;
alter table xyz add y int;
alter table xyz add z int;
update xyz set x=gs,y=gs,z=gs;
create index xyza_i1 on xyz ((x%200));
create index xyza_i2 on xyz ((y%200));
create index xyza_i3 on xyz ((z%200));
vacuum full verbose xyza;
And now:
explain analyze select gs from xyza where (x%200)=1 and (y%200)=1 and
(z%200)=1;
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Bitmap Heap Scan on xyz (cost=2782.81..2786.83 rows=1 width=4) (actual
time=134.827..505.642 rows=50000 loops=1)
Recheck Cond: (((z % 200) = 1) AND ((y % 200) = 1) AND ((x % 200) = 1))
Heap Blocks: exact=50000
-> BitmapAnd (cost=2782.81..2782.81 rows=1 width=0) (actual
time=108.712..108.712 rows=0 loops=1)
-> Bitmap Index Scan on xyza_i3 (cost=0.00..927.43 rows=50000
width=0) (actual time=22.857..22.857 rows=50000 loops=1)
Index Cond: ((z % 200) = 1)
-> Bitmap Index Scan on xyza_i2 (cost=0.00..927.43 rows=50000
width=0) (actual time=26.058..26.058 rows=50000 loops=1)
Index Cond: ((y % 200) = 1)
-> Bitmap Index Scan on xyza_i1 (cost=0.00..927.43 rows=50000
width=0) (actual time=23.079..23.079 rows=50000 loops=1)
Index Cond: ((x % 200) = 1)
Planning time: 0.340 ms
Execution time: 513.171 ms
(12 rows)
Estimates are exactly the same because it's assumed that if first clause
reduces records count by n, second by m, third by o then bringing all of
them together will reduce the result records count by n*m*o, so it is the
general behaviour, independent of whether they are statistics or not.
You suggest:
> If you're not wedded to this data representation, consider replacing that
integer flags column with a bunch of boolean columns. You might or might
not want indexes on the booleans, but > in any case ANALYZE would create
stats that would allow decent estimates for "WHERE boolval".
But, did you ever think about something like this?
CREATE STATISTICS ON (x&1) FROM xyz;
(using the syntax similar to CREATE STATISTICS from PostgreSQL 10).
Sometimes It's not possibile to divide one column into many , and as I know,
it is not worth creating an index if there are few different values in the
table.
Artur Zajac
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, November 22, 2017 4:02 PM
To: Artur Zając <azajac@ang.com.pl>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad estimates
=?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl> writes:
[ poor estimates for WHERE clauses like "(gs & 1) = 1" ]
Don't hold your breath waiting for that to get better on its own.
You need to work with the planner, not expect it to perform magic.
It has no stats that would help it discover what the behavior of that sort
of WHERE clause is; nor is there a good reason for it to think that the
selectivity of such a clause is only 0.5 rather than something more in line
with the usual behavior of an equality constraint on an integer value.
One way you could attack the problem, if you're wedded to this data
representation, is to create expression indexes on the terms "(gs & x)"
for all the values of x you use. Not only would that result in better
estimates (after an ANALYZE) but it would also open the door to satisfying
this type of query through an index search. A downside is that updating all
those indexes could make DML on the table pretty expensive.
If you're not wedded to this data representation, consider replacing that
integer flags column with a bunch of boolean columns. You might or might
not want indexes on the booleans, but in any case ANALYZE would create stats
that would allow decent estimates for "WHERE boolval".
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Bad estimates
2017-11-22 14:29 Bad estimates =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
@ 2017-11-22 15:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 25+ messages in thread
From: Laurenz Albe @ 2017-11-22 15:09 UTC (permalink / raw)
To: Artur Zając <azajac@ang.com.pl>; pgsql-performance@lists.postgresql.org
Artur Zając wrote:
> We have table created like this:
>
> CREATE TABLE xyz AS SELECT generate_series(1,10000000,1) AS gs;
>
> Now:
>
> explain analyze select * from xyz where gs&1=1;
> Seq Scan on xyz (cost=0.00..260815.38 rows=68920 width=4)
> (actual time=0.044..2959.728 rows=5000000 loops=1)
> Filter: ((gs & 1) = 1)
> Rows Removed by Filter: 5000000
[...]
> And one more clause:
>
> explain analyze select * from xyz where gs&1=1 and gs&2=2 and gs&4=4;
> Seq Scan on xyz (cost=0.00..398655.62 rows=2 width=4)
> (actual time=0.052..3329.422 rows=1250000 loops=1)
> Filter: (((gs & 1) = 1) AND ((gs & 2) = 2) AND ((gs & 4) = 4))
> Rows Removed by Filter: 8750000
> As we can see estimates differs significally from the actual records count -
> only three clauses are reducing estimated number of records from 10000000 to
> 2.
>
> I noticed that each additional clause reduces the number about 200 times and
> define DEFAULT_NUM_DISTINCT is responsible for this behaviur.
>
> I think that this variable should be lower or maybe estimation using
> DEFAULT_NUM_DISTTINCT should be done once per table.
The problem is that the expression "gs & 1" is a black box for the
optimizer; it cannot estimate how selective the condition is and falls
back to a default value that is too low.
You can create an index to
a) improve the estimate
and
b) speed up the queries:
CREATE INDEX ON xyz ((gs & 1), (gs & 2), (gs & 4));
Don't forget to ANALYZE afterwards.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2017-11-22 16:52 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-29 03:46 Re: bad estimates Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:05 ` Bruno Wolff III <bruno@wolff.to>
2003-08-29 04:09 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 04:24 ` Bruno Wolff III <bruno@wolff.to>
2003-08-29 07:01 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:17 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 12:52 ` Jeff <threshar@torgo.978.org>
2003-08-29 07:57 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:10 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 08:15 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-29 15:02 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-30 02:55 ` Bruno Wolff III <bruno@wolff.to>
2003-08-30 05:05 ` Ken Geis <kgeis@speakeasy.org>
2003-08-30 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-30 07:38 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 16:06 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 16:36 ` Sean Chittenden <sean@chittenden.org>
2003-08-29 16:56 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 17:03 ` Sean Chittenden <sean@chittenden.org>
2017-11-22 14:29 Bad estimates =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
2017-11-22 14:49 ` Re: Bad estimates Don Seiler <don@seiler.us>
2017-11-22 15:05 ` RE: Bad estimates Alex Ignatov <a.ignatov@postgrespro.ru>
2017-11-22 15:01 ` Re: Bad estimates Tom Lane <tgl@sss.pgh.pa.us>
2017-11-22 16:52 ` RE: Bad estimates =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
2017-11-22 15:09 ` Re: Bad estimates Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox