Tom Lane wrote:
Reducing random_page_cost is usually the best way to get the planner to
favor indexscans more.
Ok, I tried a bit with random_page_cost and I have set it to 1 to become
PG using the index on mitpln:
CIMSOFT=# ANALYSE mitpln;
ANALYZE
CIMSOFT=# SET random_page_cost=2;
SET
CIMSOFT=#
On Thu, Mar 17, 2005 at 09:54:29AM -0800, Josh Berkus wrote:
>
> > Yes it is. I ran experiments back in the late 90s to derive it.
> > Check the archives.
>
> H ... which list?
These look like relevant threads:
http://archives.postgresql.org/pgsql-hackers/2000-01/msg00910.php
http://archiv
Josh Berkus writes:
>> Yes it is. I ran experiments back in the late 90s to derive it.
>> Check the archives.
> H ... which list?
-hackers, no doubt. -performance didn't exist then.
regards, tom lane
---(end of broadcast)---
Tom,
> Yes it is. ÂI ran experiments back in the late 90s to derive it.
> Check the archives.
H ... which list?
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EM
On Mon, 14 Mar 2005 21:23:29 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> I think that the "reduce random_page_cost" mantra
>is not an indication that that parameter is wrong, but that the
>cost models it feeds into need more work.
One of these areas is the cost interpolation depending on correlat
Josh Berkus writes:
>> So what's going on with the empirically derived value of 4?
> It's not empirically derived;
Yes it is. I ran experiments back in the late 90s to derive it.
Check the archives.
Disks have gotten noticeably bigger since then, but I don't think
the ratio of seek time to ro
Josh Berkus writes:
> Although I can point out that you left out the fact that the disk needs to do
> a seek to find the beginning of the seq scan area, and even then some file
> fragmentation is possible. Finally, I've never seen PostgreSQL manage more
> than 70% of the maximum read rate,
Greg,
> So 800kB/s for random access reads. And 40Mb/s for sequential reads. That's
> a factor of 49. I don't think anyone wants random_page_cost to be set to 50
> though.
>
> For a high end 15k drive I see average seek times get as low as 3ms. And
> sustained transfer rates get as high as 100Mb/s
"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> What about the cache memory on the disk? Even IDE disks have some 8Mb
> cache today, which makes a lot of difference for fairly short scans.
> Even if it's just read cache. That'll bring the speed of random access
> down to a 1=1 relationship with se
> > >The "this day and age" argument isn't very convincing. Hard drive
> > >capacity growth has far outstripped hard drive seek time
> and bandwidth improvements.
> > >Random access has more penalty than ever.
> >
> > In point of fact, there haven't been noticeable seek time
> improvements
> >
David Brown <[EMAIL PROTECTED]> writes:
> Gregory Stark wrote:
>
> >The "this day and age" argument isn't very convincing. Hard drive capacity
> >growth has far outstripped hard drive seek time and bandwidth improvements.
> >Random access has more penalty than ever.
>
> In point of fact, there h
Gregory Stark wrote:
The "this day and age" argument isn't very convincing. Hard drive capacity
growth has far outstripped hard drive seek time and bandwidth improvements.
Random access has more penalty than ever.
In point of fact, there haven't been noticeable seek time improvements
for years.
Greg,
> On that note, can I raise the idea again of dropping the default
> value for random_page_cost in postgresql.conf? I think 4 is too
> conservative in this day and age. Certainly the person who will
> be negatively impacted by a default drop of 4 to 3 will be the
> exception and not the rule
Bruce Momjian wrote:
> Agreed. I think we should reduce it at least to 3.
Note that changing it from 4 to 3 or even 2 is unlikely to really change much.
Many of the plans people complain about turn out to have critical points
closer to 1.2 or 1.1.
The only reason things work out better with
Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
>
> > Reducing random_page_cost is usually the best way to get the
> > planner to favor indexscans more.
>
> On that note, can I raise the idea again of dropping the default
>
"Greg Sabino Mullane" <[EMAIL PROTECTED]> writes:
> On that note, can I raise the idea again of dropping the default
> value for random_page_cost in postgresql.conf? I think 4 is too
> conservative in this day and age. Certainly the person who will
> be negatively impacted by a default drop of 4 to
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> Reducing random_page_cost is usually the best way to get the
> planner to favor indexscans more.
On that note, can I raise the idea again of dropping the default
value for random_page_cost in postgresql.conf? I think 4 is too
conservative in this
Daniel Schuchardt <[EMAIL PROTECTED]> writes:
> i have a query plan who is bad with standard cpu_tuple_costs and good if
> I raise cpu_tuple_costs. Is it is a good practice to raise them if i
> want to force postgres to use indexes more often?
Reducing random_page_cost is usually the best way to
Hi List,
i have a query plan who is bad with standard cpu_tuple_costs and good if
I raise cpu_tuple_costs. Is it is a good practice to raise them if i
want to force postgres to use indexes more often? Or is it is better to
disable sequence scans?
CIMSOFT=# ANALYSE mitpln;
ANALYZE
CIMSOFT=# EXPL
I have forgotten this :
CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
VARCHAR AS'
BEGIN
RETURN extract(year FROM $1) || extract(month FROM $1)-1;
END'LANGUAGE plpgsql IMMUTABLE;
---(end of broadcast)---
TIP 9: the planner will
20 matches
Mail list logo
