Dear PostgresQL Experts,
I am trying to get to the bottom of some efficiency problems and hope that
you can help. The difficulty seems to be with INTERSECT expressions.
I have a query of the form
select A from T where C1 intersect select A from T where C2;
It runs in about 100 ms.
But it
On Tue, 23 Mar 2004, Phil Endecott wrote:
Dear PostgresQL Experts,
I am trying to get to the bottom of some efficiency problems and hope that
you can help. The difficulty seems to be with INTERSECT expressions.
I have a query of the form
select A from T where C1 intersect select A
On Tue, 23 Mar 2004, Stephan Szabo wrote:
On Tue, 23 Mar 2004, Phil Endecott wrote:
Dear PostgresQL Experts,
I am trying to get to the bottom of some efficiency problems and hope that
you can help. The difficulty seems to be with INTERSECT expressions.
I have a query of the form
Greg Spiegelberg wrote:
I've been following this thread closely as I have the same problem
with an UPDATE. Everything is identical here right down to the
strace output.
Has anyone found a workaround or resolved the problem? If not,
I have test systems here which I can use to help up test
I asked:
select A from T where C1 intersect select A from T where C2;
select A from T where C1 and C2;
[why isn't the first optimised into the second?]
Stephan Szabo answered:
Given a non-unique A, C1 as B5, c2 as C5 and the data:
A | B | C
1 | 6 | 1
1 | 1 | 6
The intersect gives 1 row,
On Tue, Mar 23, 2004 at 11:21:39 -0500,
Phil Endecott [EMAIL PROTECTED] wrote:
Does anyone have any suggestions about how to do this? I'd like a nice
general technique that works for all possible subqueries, as my current
composition with INTERSECT does.
One adjustment you might make is
Bruno Wolff III [EMAIL PROTECTED] writes:
On Tue, Mar 23, 2004 at 11:21:39 -0500,
Phil Endecott [EMAIL PROTECTED] wrote:
Does anyone have any suggestions about how to do this? I'd like a nice
general technique that works for all possible subqueries, as my current
composition with INTERSECT
Phil,
So I suppose I'll have to find a more sophisticated way to generate my
queries. Imagine a user interface for a search facility with various
buttons and text entry fields. At the moment, for each part of the search
that the user has enabled I create a string of SQL. I then compose
On Mon, Mar 22, 2004 at 04:05:45PM -0800, Subbiah, Stalin wrote:
being the key performance booster for postgres. what is the preferred OS
for postgres deployment if given an option between linux and solaris. As
One thing this very much depends on is what you're trying to do.
Suns have a
We are looking into Sun V210 (2 x 1 GHz cpu, 2 gig ram, 5.8Os) vs. Dell 1750
(2 x 2.4 GHz xeon, 2 gig ram, RH3.0). database will mostly be
write intensive and disks will be on raid 10. Wondering if 64bit 1 GHz to
32bit 2.4 GHz make a big difference here.
Thanks!
-Original Message-
From:
What bus speeds?
533MHz on the 32-bit Intel will give you about 4.2Gbps of IO throughput...
I think the Sun will be 150MHz, 64bit is 2.4Gbps of IO. Correct me if i am wrong.
Thanks,
Anjan
-Original Message-
From: Subbiah, Stalin [mailto:[EMAIL PROTECTED]
If it's going to be write intensive then the RAID controller will be the most
important thing. A dual p3/500 with a write-back
cache will smoke either of the boxes you mention using software RAID on write
performance.
As for the compute intensive side (complex joins sorts etc), the Dell will
Hello,
I am using postgres 7.4.2 as a backend for geocode data for a mapping
application. My question is why can't I get a consistent use of my indexes
during a query, I tend to get a lot of seq scan results.
I use a standard query:
SELECT lat, long, mac, status FROM (
SELECT text(mac) as
I'm going to ask because someone else surely will:
Do you regularily vacuum/analyze the database?
Woody Woodring wrote:
Hello,
I am using postgres 7.4.2 as a backend for geocode data for a mapping
application. My question is why can't I get a consistent use of my indexes
during a query, I tend
Matt, Stalin,
As for the compute intensive side (complex joins sorts etc), the Dell will
most likely beat the Sun by some distance, although
what the Sun lacks in CPU power it may make up a bit in memory bandwidth/
latency.
Personally, I've been unimpressed by Dell/Xeon; I think the Sun
A Palmblad [EMAIL PROTECTED] writes:
GroupAggregate (cost=0.00..338300.34 rows=884 width=345) (actual
time=86943.272..382718.104 rows=3117 loops=1)
- Merge Join (cost=0.00..93642.52 rows=1135610 width=345) (actual
time=0.148..24006.748 rows=1120974 loops=1)
You do not have a planning
On Tue, 23 Mar 2004, Josh Berkus wrote:
Matt, Stalin,
As for the compute intensive side (complex joins sorts etc), the Dell will
most likely beat the Sun by some distance, although
what the Sun lacks in CPU power it may make up a bit in memory bandwidth/
latency.
Personally, I've
-- Forwarded Message --
Subject: FreeBSD, PostgreSQL, semwait and sbwait!
Date: March 23, 2004 12:02 pm
From: Jason Coene [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Hello all,
We're having a substantial problem with our FreeBSD 5.2 database server
running PostgreSQL - it's
As anyone done performance benchmark testing with solaris sparc/intel linux.
I once read a post here, which had benchmarking test results for using
different filesystem like xfs, ext3, ext2, ufs etc. i couldn't find that
link anymore and google is failing on me, so anyone have the link handy.
On Tue, Mar 23, 2004 at 08:53:42PM -, [EMAIL PROTECTED] wrote:
is way down the priority list compared with IO throughput, stability,
manageability, support, etc etc.
Indeed, if our Suns actually diabled the broken hardware when they
died, fell over, and rebooted themselves, I'd certainly
Subbiah, Stalin wrote:
As anyone done performance benchmark testing with solaris sparc/intel linux.
I once read a post here, which had benchmarking test results for using
different filesystem like xfs, ext3, ext2, ufs etc. i couldn't find that
link anymore and google is failing on me, so anyone
Darcy Buskermolen [EMAIL PROTECTED] writes:
The database server is a dual P4-2.8 w/ HT enabled (kernel finds 4
processors), 2GB RAM, 4 disk Serial ATA on 3ware RAID, gigabit Ethernet
connection to web servers. It's running FreeBSD 5.2 and PostgreSQL 7.4.1.
Hm. What happens if you turn off
Darcy,
I suggest getting this person over here instead.They have a *lot* to learn
about tuning PostgreSQL.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Rosser Schwarz wrote:
Greg Spiegelberg wrote:
I've been following this thread closely as I have the same problem
with an UPDATE. Everything is identical here right down to the
strace output.
Has anyone found a workaround or resolved the problem? If not,
I have test systems here which I can
Are you talking about
http://www.potentialtech.com/wmoran/postgresql.php#conclusion
- Original Message -
From: Subbiah, Stalin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; Matt Clark [EMAIL PROTECTED]; Subbiah, Stalin
[EMAIL PROTECTED]; 'Andrew Sullivan' [EMAIL PROTECTED];
[EMAIL PROTECTED]
Greg Spiegelberg [EMAIL PROTECTED] writes:
RedHat 7.3 + Kernel 2.4.24 + ext3 + PostgreSQL 7.3.5
Please try 7.4.
regards, tom lane
---(end of broadcast)---
TIP 9: the
Tom,
Hm. What happens if you turn off the hyperthreading?
Forget hyperthreading. Look at their postgresql.conf settings. 8mb shared
mem, 16mb sort mem per connection for 512 connections, default
effective_cache_size.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus [EMAIL PROTECTED] writes:
Forget hyperthreading. Look at their postgresql.conf settings. 8mb shared
mem, 16mb sort mem per connection for 512 connections, default
effective_cache_size.
They could well be going into swap hell due to the oversized sort_mem,
but that didn't
Yep. Thanks Bill.
-Original Message-
From: Bill Moran [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 23, 2004 2:10 PM
To: Subbiah, Stalin
Cc: [EMAIL PROTECTED]
Subject: Re: [PERFORM] [ADMIN] Benchmarking postgres on Solaris/Linux
Subbiah, Stalin wrote:
As anyone done performance
A Palmblad [EMAIL PROTECTED] writes:
GroupAggregate (cost=0.00..338300.34 rows=884 width=345) (actual
time=86943.272..382718.104 rows=3117 loops=1)
- Merge Join (cost=0.00..93642.52 rows=1135610 width=345) (actual
time=0.148..24006.748 rows=1120974 loops=1)
I think the reason you're
Mark,
It might be worth considering Apple if you want a 64-bit chip that has a
clock speed comparable to Intel's - the Xserv is similarly priced to Sun
V210 (both dual cpu 1U's).
Personally I'd stay *far* away from the XServs until Apple learns to build
some real server harware.The
And we also created rules to allow update, delete, and insert on those
views so that they looked like tables. The reason we did this is
because we ran into issues with too many open files during pg_dump when
we had thousands of tables instead of about 1 hundred tables and
thousands of views.
32 matches
Mail list logo