Jenny Zhang <[EMAIL PROTECTED]> writes:
> ... It seems to me that small
> effective_cache_size favors the choice of nested loop joins (NLJ)
> while the big effective_cache_size is in favor of merge joins (MJ).
No, I wouldn't think that, because a nestloop plan will involve repeated
fetches of
Thanks for your prompt reply.
On Thu, 2003-09-18 at 16:19, Matt Clark wrote:
> > We thought the large effective_cache_size should lead us to better
> > plans. But we found the opposite.
>
> Maybe it's inappropriate for little old me to jump in here, but the plan
> isn't usually that important com
> We thought the large effective_cache_size should lead us to better
> plans. But we found the opposite.
Maybe it's inappropriate for little old me to jump in here, but the plan
isn't usually that important compared to the actual runtime. The links you
give show the output of 'explain' but not 'e
Our hardware/software configuration:
kernel: 2.5.74
distro: RH7.2
pgsql: 7.3.3
CPUS: 8
MHz:700.217
model: Pentium III (Cascades)
memory: 829 kB
shmmax: 3705032704
We did several sets of runs(repeating runs with the same database
parameters) and have the following observation:
1. With
Thanks Josh!
But, the EXISTS version doesn't really give me what I want...all rows in tbl that
match the date of the subquery.
But, using the DISTINCT does make sense.
Thanks again to all who helped!
-Original Message-
From: Josh Berkus <[EMAIL PROTECTED]>
Sent: Sep 18, 2003 10:34 AM
Josh Berkus <[EMAIL PROTECTED]> writes:
> Suggestion 3: There was an issue in 7.3 with table rows which are overly broad
> -- some problems with PSQL, I believe.
Not sure about PSQL, but I think there still are some performance issues
in the backend with SELECTs involving more than a couple hundr
Rhaoni,
> I could .. but this way I wont be used because Oracle doesn't accept such
> sintax ! I changed gsames00.ano_mes from varchar to text ! But it still not
> fast enough to take Oracle's place !!!
> I still trying to do so ...
Well, your basic problem is that performance tuning for *any* da
Joseph,
> I hope this to be a simple question. I have need to simply read the first
> row in a given table. Right now, I have some legacy code that selects all
> rows in a table just to see if the first row has a certain value.
Your problem is conceptual: in SQL, there is no "first" row.
If
Rhaoni,
> ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'MM') AND ...
>Then it uses the gsames00 index instead of a SeqScan 'cuz it is
> camparing same data type, but .. I don't want to create this function 'cuz
> this aplication is used with Oracle too.
You should have said that
Dear list,
I hope this to be a simple question. I have need to simply read the first
row in a given table. Right now, I have some legacy code that selects all
rows in a table just to see if the first row has a certain value.
The code is seeking to see if an update has been run or not. A hypoth
Laurette,
> >SELECT t1.code, t1.id, t1.date_of_service
> > FROM tbl t1 INNER JOIN
> > (SELECT DISTINCT date_of_service
> > FROM tbl
> > WHERE xxx >= '29800' AND xxx <= '29909'
> > AND code = 'XX'
> > ) AS t2 ON (t1.date_of_service = t2.date_of_service)
> > WHERE
Guys,
I also wrote a perl script that reindexes all tables, if anyone can't get
reindexdb working or find it for 7.2.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Alexander,
> I am in the process of creating a database design in which LOTS of data
> need to be modelled.
>
> For instance, I need to store data about products. Every product has LOTS
> of properties, well over a hundred.
> Do any of you know if and how PostgreSQL would prefer one approach over
Rhaoni Chiu Pereira <[EMAIL PROTECTED]> writes:
> I need to know if there is a way to set the to_char output to varchar instead of
> text !
Why don't you change the datatype of ano_mes to text, instead? It's
unlikely your application would notice the difference. (You could set
a CHECK constraint
On Thu, 18 Sep 2003 13:23:37 +0800, "Christopher Kings-Lynne"
>To the original poster: You did not provide a lot of information, but
>the following suggestions might give you an idea ...
>
Yes, sorry about that. But in my query for a set of dates returned from the subquery
I would then like
(I've sent him reindexdb off-list)
Chris
On Thu, 18 Sep 2003, Oliver Scheit wrote:
> >> > It's part of postgresql 7.3. Just get it from the 7.3
> >> > contrib dir - it works fine with 7.2
> >> That's nice to hear. Thanx for that info.
>
> > That's alright - cron job it for once a month - that's
I solve this problem doing this:
create function date_to_mm( timestamp ) returns gsames00.ano_mes%type as
'select to_char($1, ''MM'');
' language sql immutable strict;
And changing the SQL where clause:
... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'MM') AND ...
to:
...
>> > It's part of postgresql 7.3. Just get it from the 7.3
>> > contrib dir - it works fine with 7.2
>> That's nice to hear. Thanx for that info.
> That's alright - cron job it for once a month - that's what
> I do. Basically the problem is that in certain cases
> (monotonically increasing seria
On Thu, 18 Sep 2003 13:23:37 +0800, "Christopher Kings-Lynne"
<[EMAIL PROTECTED]> wrote:
>Why can't you just go:
>
>select code, id, name, date_of_service from tbl where xxx <= 29800 and xx >=
>29909 and code='XX' and client_code='XX' order by id, date_of_service;
Because (ignoring conditions on c
> #rpm -qa|grep postgres
> postgresql-server-7.2.3-5.73
> postgresql-libs-7.2.3-5.73
> postgresql-devel-7.2.3-5.73
> postgresql-7.2.3-5.73
>
> What package am I missing?
It's part of postgresql 7.3. Just get it from the 7.3 contrib dir - it
works fine with 7.2
Note that this index growth problem
> Yes, there is reindexdb :)
Not on my machine. (RH 7.3)
#rpm -qa|grep postgres
postgresql-server-7.2.3-5.73
postgresql-libs-7.2.3-5.73
postgresql-devel-7.2.3-5.73
postgresql-7.2.3-5.73
What package am I missing?
regards,
Oliver Scheit
---(end of broadcast)-
> 3) using PG 7.3 or less, you will also need to REINDEX these
>tables+indexes often (daily?). This issue will go away
>in 7.4, which should make you an early adopter of 7.4.
Try monthly maybe.
> Is this true? Haven't heard of this before.
> If so, how can this be managed in a cronjob?
3) using PG 7.3 or less, you will also need to REINDEX these
tables+indexes often (daily?). This issue will go away
in 7.4, which should make you an early adopter of 7.4.
Is this true? Haven't heard of this before.
If so, how can this be managed in a cronjob?
For the hourly VACUUM there's
Hi guys,
I am in the process of creating a database design in which LOTS of data need
to be modelled.
For instance, I need to store data about products. Every product has LOTS of
properties, well over a hundred.
So I'm wondering. What's the best approach here, performance wise? Just
create one P
24 matches
Mail list logo
