Ken Geis <[EMAIL PROTECTED]> 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 so
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 pre
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
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.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please
> >>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
> >thin
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 Manfr
> >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 th
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
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
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
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 whil
> > 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
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
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
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 | intege
On Thu, Aug 28, 2003 at 21:09:00 -0700,
Ken Geis <[EMAIL PROTECTED]> 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 pri
Bruno Wolff III wrote:
On Thu, Aug 28, 2003 at 20:46:00 -0700,
Ken Geis <[EMAIL PROTECTED]> 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
On Thu, Aug 28, 2003 at 20:46:00 -0700,
Ken Geis <[EMAIL PROTECTED]> 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
> (s
Bruno Wolff III wrote:
On Thu, Aug 28, 2003 at 20:00:32 -0700,
Ken Geis <[EMAIL PROTECTED]> 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 us
On Thu, Aug 28, 2003 at 20:00:32 -0700,
Ken Geis <[EMAIL PROTECTED]> 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
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 ta
On Thu, Aug 28, 2003 at 19:50:38 -0700,
Ken Geis <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III wrote:
> >On Thu, Aug 28, 2003 at 17:10:31 -0700,
> > Ken Geis <[EMAIL PROTECTED]> wrote:
> >
> >>The query I want to run is
> >>
> >>select stock_id, min(price_date) from day_ends group by stock_id;
>
Bruno Wolff III wrote:
On Thu, Aug 28, 2003 at 17:10:31 -0700,
Ken Geis <[EMAIL PROTECTED]> wrote:
The query I want to run is
select stock_id, min(price_date) from day_ends group by stock_id;
The fast way to do this is:
select distinct on (stock_id) stock_id, price_date
order by stock_id, pri
On Thu, Aug 28, 2003 at 17:10:31 -0700,
Ken Geis <[EMAIL PROTECTED]> wrote:
> The query I want to run is
>
> select stock_id, min(price_date) from day_ends group by stock_id;
The fast way to do this is:
select distinct on (stock_id) stock_id, price_date
order by stock_id, price_date;
> Also
I'm surprised at the effort pgsql requires to run one of my queries. I
don't know how to tune this query.
Column | Type | Modifiers
+--+---
the_id | integer | not null
the_date | date | not null
num1 | numeric(9,4) |
num2
25 matches
Mail list logo
