On Sun, 2005-03-20 at 01:14 -0500, Greg Stark wrote:
> Josh Berkus writes:
>
> > -- INSERT INTO should automatically create new partitions where necessary
> > new tables should automatically inherit all constraints, indexes,
> > keys of "parent" table
>
> I think you're goin
(B
(B
(BHi everyone,
(BI hope it is the correct newsletter for this question.
(B
(BCan I use an index on a varchar column to optimize the SELECT queries that
(Buse " column LIKE 'header%' "?
(BIf yes what is the best tree algotithm to use ?
(B
(BI don't care about optimising INSERT,
On Tue, 22 Mar 2005 18:22:24 +0900, Layet Benjamin
<[EMAIL PROTECTED]> wrote:
> Can I use an index on a varchar column to optimize the SELECT queries that
> use " column LIKE 'header%' "?
> If yes what is the best tree algotithm to use ?
Yes, that is the correct place. The best tree algorithm
On Mon, 21 Mar 2005 14:59:56 -0800, Josh Berkus wrote:
> > If by not practical you mean, "no one has implemented a multivariable
> > testing approach," I'll agree with you. But multivariable testing is
> > definitely a valid statistical approach to solving just such problems.
> Well, not practical
Can I use an index on a varchar column to optimize the SELECT queries
that
use " column LIKE 'header%' "?
Yes
If yes what is the best tree algotithm to use ?
Btree
Note that if you want case insensitive matching you need to make an index
on lower(column) and SELECT WHERE lower(column
On E, 2005-03-21 at 09:55 -0800, Josh Berkus wrote:
> Stacy,
>
> > Luckily they that had the chance to work with a truly fantastic DBA (the
> > author of an Oracle Press performance tuning book even) before they could
> > switch back. He convinced them to make some of their indexes global.
> > Pe
On P, 2005-03-20 at 00:52 +0100, PFC wrote:
> > tableoid would accomplish that already, assuming that the "partitioned
> > table" is effectively a view on separate physical tables.
> >
> > regards, tom lane
>
> Very good.
>
> Also note the possibility to mark a par
On L, 2005-03-19 at 12:02 -0800, Josh Berkus wrote:
> Folks,
>
> I may (or may not) soon have funding for implementing full table partitioning
> in PostgreSQL.
If you don't get it, contact me as there is a small possibility that I
know a company interested enough to fund (some) of it :)
> I th
On L, 2005-03-19 at 19:03 -0500, Tom Lane wrote:
> Josh Berkus writes:
> >>> -- INSERT INTO should automatically create new partitions where necessary
> >>> -- DELETE FROM should automatically drop empty partitions
> >>
> >> I am not sure I agree with either of those, and the reason is that they
On L, 2005-03-19 at 23:47 -0500, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Hmm. You are right, but without that we won't be able to enforce
> > uniqueness on the partitioned table (we could only enforce it on each
> > partition, which would mean we can't partition on anythin
Greg Stark wrote:
Josh Berkus writes:
That's not really practical. There are currently 5 major query tuning
parameters, not counting the memory adjustments which really can't be left
out. You can't realistically test all combinations of 6 variables.
I don't think it would be very hard at all
Hi,
I'm looking for a *fast* solution to search thru ~ 4 million records of
book descriptions. I've installed PostgreSQL 8.0.1 on a dual opteron
server with 8G of memory, running Linux 2.6. I haven't done a lot of
tuning on PostgreSQL itself, but here's the settings I have changed so far:
share
On Tue, 22 Mar 2005, Rick Jansen wrote:
Hi,
I'm looking for a *fast* solution to search thru ~ 4 million records of book
descriptions. I've installed PostgreSQL 8.0.1 on a dual opteron server with
8G of memory, running Linux 2.6. I haven't done a lot of tuning on PostgreSQL
itself, but here's th
On Tue, 22 Mar 2005 15:36:11 +0300 (MSK), Oleg Bartunov wrote:
> On Tue, 22 Mar 2005, Rick Jansen wrote:
>
> > Hi,
> >
> > I'm looking for a *fast* solution to search thru ~ 4 million records of book
> > descriptions. I've installed PostgreSQL 8.0.1 on a dual opteron server with
> > 8G of memory,
On Mon, Mar 21, 2005 at 08:26:24PM +0200, Hannu Krosing wrote:
> On P, 2005-03-20 at 00:52 +0100, PFC wrote:
> > Also note the possibility to mark a partition READ ONLY. Or even a
> > table.
> Would we still need regular VACUUMing of read-only table to avoid
> OID-wraparound ?
You could VA
On Tue, 22 Mar 2005, Mike Rylander wrote:
And Oleg should know. Unless I'm mistaken, he (co)wrote tsearch2.
You're not mistaken :)
Other than shared buffers, I can't imagine what could be causing that
kind of slowness. EXPLAIN ANALYZE, please?
tsearch2 config's also are very important. I've seen
Mike Rylander wrote:
On Tue, 22 Mar 2005 15:36:11 +0300 (MSK), Oleg Bartunov wrote:
use something like
echo "15000" > /proc/sys/kernel/shmmax
to increase shared memory. In your case you could dedicate much more
memory.
Regards,
Oleg
Thanks, I'll check that out.
And Oleg
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Greg Stark) wrote:
> I don't think it would be very hard at all actually.
>
> It's just a linear algebra problem with a bunch of independent
> variables and a system of equations. Solving for values for all of
> them is a straightforward
I get the following output from explain analyze on a certain subset of
a large query I'm doing.
>From the looks of it, I need to increase how often postgres uses an
index over a seq scan, but I'm not sure how to do that. I looked
through the run-time configuration docs on the website, but didn't
On Tue, Mar 22, 2005 at 08:09:40 -0500,
Christopher Browne <[EMAIL PROTECTED]> wrote:
>
> Are you certain it's a linear system? I'm not. If it was a matter of
> minimizing a linear expression subject to some set of linear
> equations, then we could model this as a Linear Program for which
> th
On Tue, 22 Mar 2005 14:25:19 +0100, Rick Jansen <[EMAIL PROTECTED]> wrote:
>
> ilab=# explain analyze select count(titel) from books where idxfti @@
> to_tsquery('default', 'buckingham | palace');
> QUERY PLAN
> --
Mike,
no comments before Rick post tsearch configs and increased buffers !
Union shouldn't be faster than (term1|term2).
tsearch2 internals description might help you understanding tsearch2
limitations.
See http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals
Also, don't miss my n
On T, 2005-03-22 at 09:10 -0400, Alvaro Herrera wrote:
> On Mon, Mar 21, 2005 at 08:26:24PM +0200, Hannu Krosing wrote:
> > On P, 2005-03-20 at 00:52 +0100, PFC wrote:
>
> > > Also note the possibility to mark a partition READ ONLY. Or even a
> > > table.
>
> > Would we still need regular VACU
Oleg Bartunov wrote:
Mike,
no comments before Rick post tsearch configs and increased buffers !
Union shouldn't be faster than (term1|term2).
tsearch2 internals description might help you understanding tsearch2
limitations.
See http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals
Richard Huxton writes:
> You'd only need to log them if they diverged from expected anyway. That should
> result in fairly low activity pretty quickly (or we're wasting our time).
> Should they go to the stats collector rather than logs?
I think you need to log them all. Otherwise when you go to
Alex Turner wrote:
I get the following output from explain analyze on a certain subset of
a large query I'm doing.
Try increases the statistics on the listprice column with alter
table and then re-run analyze.
alter table foo alter column set statistics
Sincerely,
Joshua D. Drake
From the look
On Tue, 22 Mar 2005, Rick Jansen wrote:
Oleg Bartunov wrote:
Mike,
no comments before Rick post tsearch configs and increased buffers !
Union shouldn't be faster than (term1|term2).
tsearch2 internals description might help you understanding tsearch2
limitations.
See http://www.sai.msu.su/~megera
Hannu,
> If you don't get it, contact me as there is a small possibility that I
> know a company interested enough to fund (some) of it :)
Enough people have been interested in this that if we get our acts together,
we may do it as multi-funded. Easier on our budget ...
> As these are already
Christopher Browne <[EMAIL PROTECTED]> writes:
> Are you certain it's a linear system?
If you just consider the guc parameters that tell postgres how long various
real world operations take (all the *_cost parameters) then it's a linear
system. It has to be. The resulting time is just a sum of
Greg Stark wrote:
Richard Huxton writes:
You'd only need to log them if they diverged from expected anyway. That should
result in fairly low activity pretty quickly (or we're wasting our time).
Should they go to the stats collector rather than logs?
I think you need to log them all. Otherwise when
PFC <[EMAIL PROTECTED]> writes:
>> Can I use an index on a varchar column to optimize the SELECT queries
>> that use " column LIKE 'header%' "?
> Yes
> Note that if you want case insensitive matching you need to make an
> index
> on lower(column) and SELECT WHERE lower(column) L
Hi everyone,
I'm developping a web decisonnal application based
on
-Red Hat 3 ES
-Postgresql 8.0.1
-Dell poweredge 2850, Ram 2Gb, 2 procs, 3 Ghz, 1Mb
cache and 4 disks ext3 10,000 r/mn
I am alone in the box and there is not any
crontab.
I have 2 databases (A and B) with exactly the same
Hi Patrick,
How is configured your disk array? Do you have a Perc 4?
Tip: Use reiserfs instead ext3, raid 0+1 and deadline I/O scheduler in
kernel linux 2.6
Atenciosamente,
Gustavo Franklin Nóbrega
Infra-Estrutura e Banco de Dados
Planae Tecnologia da Informação
(+55) 14 3224-3066 Ramal
Christopher Browne <[EMAIL PROTECTED]> writes:
> Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Greg Stark)
> wrote:
>> It's just a linear algebra problem with a bunch of independent
>> variables and a system of equations. Solving for values for all of
>> them is a straightforward p
Please post the results of that query as run through EXPLAIN ANALYZE.
Also, I'm going to reply to this on pgsql-performance, which is
probably where it better belongs.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite
Greg Stark <[EMAIL PROTECTED]> writes:
> Christopher Browne <[EMAIL PROTECTED]> writes:
>> Are you certain it's a linear system?
> If you just consider the guc parameters that tell postgres how long various
> real world operations take (all the *_cost parameters) then it's a linear
> system. It
Patrick Vedrines wrote:
Hi everyone,
I'm developping a web decisonnal application based on -Red Hat 3 ES
-Postgresql 8.0.1 -Dell poweredge 2850, Ram 2Gb, 2 procs, 3 Ghz, 1Mb
cache and 4 disks ext3 10,000 r/mn I am alone in the box and there is
not any crontab.
I have 2 databases (A and B) with ex
This helps a bit when I set it to 1000 - but it's still pretty bad:
I will use an index 220-300, but not 200-300.
Alex
trendmls=# explain analyze select listnum from propmain where
listprice<=30 and listprice>=20;
QUERY PLAN
On Tue, 2005-03-22 at 14:36 -0500, Alex Turner wrote:
> I will use an index 220-300, but not 200-300.
> ...
> Seq Scan on propmain (cost=0.00..15517.56 rows=6842 width=4) (actual
> time=0.039..239.760 rows=6847 loops=1)
> ...
> Index Scan using propmain_listprice_i on propmain
> (cost=0.00..22
We've recently moved our pgsql installation and DBs to a Solaris 8
machine with striped and mirrored ufs filesystem that houses the DB
data. We are now seeing terrible performance and the bottleneck is no
doubt disk I/O.
We've tried modifying a tunables related to ufs, but it doesn't seem
to be h
On Tue, 2005-03-22 at 14:44 -0600, Brandon Metcalf wrote:
> We've recently moved our pgsql installation and DBs to a Solaris 8
> machine with striped and mirrored ufs filesystem that houses the DB
> data. We are now seeing terrible performance and the bottleneck is no
> doubt disk I/O.
>
> We've
Brandon Metcalf wrote:
We've recently moved our pgsql installation and DBs to a Solaris 8
machine with striped and mirrored ufs filesystem that houses the DB
data. We are now seeing terrible performance and the bottleneck is no
doubt disk I/O.
We've tried modifying a tunables related to ufs, but i
s == [EMAIL PROTECTED] writes:
s> Try setting
s> set ufs:ufs_WRITES=0
s> in /etc/system and rebooting, which basically says "any amount of disk
s> IO can be outstanding". There's a tunables doc on docs.sun.com that
s> explains this option.
s> Also, logging UFS might help with some of the m
s == [EMAIL PROTECTED] writes:
s> What are you using to create your raid?
Hm. I didn't set this up. I'll have to check.
s> You say it is "no doubt disk
s> I/O" - does iostat confirm this? A lot of performance issues are related
s> to the size of the stripe you chose for the striped portion
Brandon Metcalf wrote:
We've recently moved our pgsql installation and DBs to a Solaris 8
machine with striped and mirrored ufs filesystem that houses the DB
data. We are now seeing terrible performance and the bottleneck is no
doubt disk I/O.
We've tried modifying a tunables related to ufs, but i
Tom Lane <[EMAIL PROTECTED]> writes:
> Christopher Browne <[EMAIL PROTECTED]> writes:
> > Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Greg Stark)
> > wrote:
> >> It's just a linear algebra problem with a bunch of independent
> >> variables and a system of equations. Solving for
Greg Stark <[EMAIL PROTECTED]> writes:
> The time spent in real-world operations like random page accesses, sequential
> page accesses, cpu operations, index lookups, etc, are all measurable
> quantities. They can be directly measured or approximated by looking at the
> resulting net times.
That's
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 22, 2005 3:48 PM
> To: Greg Stark
> Cc: Christopher Browne; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] What about utility to calculate planner cost
> constants?
> [...]
> The difficulty wit
On Tue, 2005-03-22 at 19:08 +0100, Patrick Vedrines wrote:
> I have 2 databases (A and B) with exactly the same schemas:
> -one main table called "aggregate" having no indexes and supporting
> only SELECT statements (loaded one time a month with a new bundle of
> datas). Row size # 200 bytes (50 c
> -Original Message-
> From: Dave Held
> Sent: Tuesday, March 22, 2005 4:16 PM
> To: Tom Lane
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] What about utility to calculate planner cost
> constants?
> [...]
> Then instead of building a fixed cost model, why not evolve
> a
Tom Lane wrote:
And you can't just dismiss the issue of wrong cost models and say we can
get numbers anyway.
Is there a way to see more details about the cost estimates.
EXPLAIN ANALYZE seems to show the total time and rows; but not
information like how many disk pages were accessed.
I get the feel
I'm guessing your data is actually more "clustered" than the
"correlation" stastic thinks it is.
Alex Turner wrote:
> trendmls=# explain analyze select listnum from propmain where
> listprice<=30 and listprice>=20;
Is that a database of properties like land/houses?
If
I'm guessing your data is actually more "clustered" than the
"correlation" statistic thinks it is.
Alex Turner wrote:
> trendmls=# explain analyze select listnum from propmain where
> listprice<=30 and listprice>=20;
Is that a database of properties like land/houses?
[EMAIL PROTECTED] ("Dave Held") writes:
>> -Original Message-
>> From: Tom Lane [mailto:[EMAIL PROTECTED]
>> Sent: Tuesday, March 22, 2005 3:48 PM
>> To: Greg Stark
>> Cc: Christopher Browne; pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] What about utility to calculate planner
Added to TODO:
* Support table partitioning that allows a single table to be stored
in subtables that are partitioned based on the primary key or a WHERE
clause
---
Josh Berkus wrote:
> Hannu,
>
> > If you don't get i
Hi guys,
We are in the process of buying a new dell server.
Here is what we need to be able to do:
- we need to be able to do queries on tables that has 10-20 millions
of records (around 40-60 bytes each row) in less than 5-7 seconds.
We also need the hardware to be able to handle up to 50 millions
On Tue, 2005-03-22 at 17:32 -0800, Junaili Lie wrote:
> Here is what we are thinking:
> - Dual Xeon 2.8 Ghz
> - 4GB DDR2 400 Mhz Dual Ranked DIMMS (is dual ranked or single ranked
> makes any differences in terms of performance?). Do you guys think 4GB
> is reasonably enough?
> - 73 GB 15k RPM Ultr
57 matches
Mail list logo
