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,
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
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
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
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, to
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,
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
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 use
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
(stock_id,
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
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
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:
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
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;
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
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
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
17 matches
Mail list logo
