On Thursday 30 Oct 2003 4:53 am, you wrote:
> <[EMAIL PROTECTED]> writes:
> > Actually PostgreSQL is at par with MySQL when the query is being
> > Properly Written(simplified)
>
> These are not the same query, though. Your original looks like
Yes that was an optimisation on haste the simplifica
Dear Tom,
Can you please have a Look at the below and suggest why it apparently puts
7.3.4 on an infinite loop . the CPU utilisation of the backend running it
approches 99%.
Query:
I have tried my best to indent it :)
SELECT DISTINCT main.* FROM
(
(
(
(
Tickets
Are you seeing this question as totally off-topic in this list, or there is
really no one who knows something about indexing "is null" bits in postgres?
Regards
CH
> Hi,
>
> suppose, for simplicity, there is a table with index like this:
>
> create table TABLE1 (
> A integer
> );
> create inde
[EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote:
> Can you please have a Look at the below and suggest why it
> apparently puts 7.3.4 on an infinite loop . the CPU utilisation of
> the backend running it approches 99%.
What would be useful, for this case, would be to provide the query
plan, perhaps
try this:
EXPLAIN [ANALYZE] SELECT a FROM table1 WHERE a IS NULL OR a>2;
SET enable_seqscan TO off;
EXPLAIN [ANALYZE] SELECT a FROM table1 WHERE a IS NULL OR a>2;
and compare the costs and times of both executions. This will tell you why postgresql is not using an index.
For example, if you
On Thu, Oct 30, 2003 at 12:34:15 +0100,
Cestmir Hybl <[EMAIL PROTECTED]> wrote:
> Are you seeing this question as totally off-topic in this list, or there is
> really no one who knows something about indexing "is null" bits in postgres?
There was some talk about IS NULL not being able to use ind
> [EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote:
>> Can you please have a Look at the below and suggest why it
>> apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend
>> running it
>> approches 99%.
>
> What would be useful, for this case, would be to provide the query
On Wed, 29 Oct 2003, Rob Nagler wrote:
> Greg Stark writes:
> > > > SELECT a, (SELECT name FROM t2 WHERE t2.f2 = t1.f2)
> > > > FROM t1
> > > > GROUP BY f2
> > >
> > > This doesn't solve the problem. It's the GROUP BY that is doing the
> > > wrong thing. It's grouping, then aggregating.
> >
In the last exciting episode, [EMAIL PROTECTED] wrote:
>> [EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote:
>>> Can you please have a Look at the below and suggest why it
>>> apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend
>>> running it
>>> approches 99%.
>>
>> What wo
"Cestmir Hybl" <[EMAIL PROTECTED]> writes:
>> In fact, I need to filter by expression ((A is null) or (A > const)).
I wonder whether you shouldn't reconsider your data representation.
Perhaps the condition you are using "null" for would be better
represented by setting A to infinity. (The float a
Josh Berkus writes:
> I hope that you'll stay current with PostgreSQL developments so that you can
> do a similarly thourough evaluation for your next project.
Oh, no worries. This project just happens to be a tough one. We're
heavily invested in Postgres. Other projects we maintain that use
P
Hi ,
Here are the Execution Plans ,
Sorry for the delay .
Regds
Mallah
On PostgreSQL 7.3.4
rt3=# explain SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance))
JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.Object
Tom Lane wrote:
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
SELECT DISTINCT main.* FROM
(
(
(
(
Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)
) JOIN
Principals as Principals_2 ON ( Groups_1.id = Princi
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
> SELECT DISTINCT main.* FROM
> (
>(
> (
> (
> Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)
> ) JOIN
> Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)
scott.marlowe writes:
> t2 was 'vacuum full'ed and analyzed, right? Just guessing.
Fresh import. I've been told this includes a ANALYZE.
Rob
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Rob Nagler <[EMAIL PROTECTED]> writes:
> When vacuum is running, it's going through the entire
> database, and that pretty much trashes all other queries, especially
> DSS queries. As always it is just software, and there's got to be
> 80/20 solution.
One thing that's been discussed but not yet t
On Thu, 30 Oct 2003, Rob Nagler wrote:
> The vacuum problem is very serious for the problematic database to the
> point that one of my customer's customers said:
>
> However, I am having a hard time understanding why the system is so
> slow... from my perspective it seems like you have so
On Thu, 30 Oct 2003, Rob Nagler wrote:
> scott.marlowe writes:
> > t2 was 'vacuum full'ed and analyzed, right? Just guessing.
>
> Fresh import. I've been told this includes a ANALYZE.
You should probably run analyze by hand just to be sure. If the planner
is using an index scan on a table wi
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
> -> Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=164)
> Filter: ((effectiveid = id) AND (("type")::text = 'ticket'::text) AND
> (((status)::text = 'new'::text) OR ((status)::text = 'open'::text)))
This query has to read through
Rob,
> I have had a lot push back from the core Postgres folks on the idea of
> planner hints, which would go a long way to solve the performance
> problems we are seeing.
I can tell you that the general reaction that you'll get is "let's fix the
problems with the planner instead of giving the
Josh Berkus wrote:
> > Our new project is large, high-profile, but not as data intensive as
> > the problematic one. We are willing to commit significant funding and
> > effort to make Postgres faster. We are "business value" driven. That
> > means we solve problems practically instead of theore
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
> rt3=# explain
>
> SELECT DISTINCT main.*
> FROM (((
> (Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance))
> JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)
> ) JOIN C
explain analyze of original Query:
rt3=# explain analyze SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( P
Hi,
Old: Post 7.3.2, P4 1.8, 1 MB RAM, 2 x IDE SW RAID 1, RedHat 8
New: Post 7.3.4, Xeon 2.4, 1 MB RAM, 2 x SCSI 15k SW RAID 1, RedHat 9
Both use: Only postgresql on server. Buffers = 8192, effective cache = 10
In old plataform the free and vmstat reports no use of swap.
In new, the swap is
On Thu, 30 Oct 2003, alexandre :: aldeia digital wrote:
> Hi,
>
> Old: Post 7.3.2, P4 1.8, 1 MB RAM, 2 x IDE SW RAID 1, RedHat 8
> New: Post 7.3.4, Xeon 2.4, 1 MB RAM, 2 x SCSI 15k SW RAID 1, RedHat 9
>
> Both use: Only postgresql on server. Buffers = 8192, effective cache = 10
>
> In old p
Hi,
I'am having major performance issues with post gre 7.3.1 db. Kindly suggest all
the possible means by which i can optimize the performance of this database. If not
all, some ideas (even if they are common) are also welcome. There is no optimisation
done to the default configuration of
Tom Lane writes:
> Rob Nagler <[EMAIL PROTECTED]> writes:
> > q5 and q6 are too complex to discuss here,
>
> How do you expect us to get better if you don't show us the problems?
With all due respect and thanks for the massive amount of help, I have
presented the problems. q5 and q6 are a subset
On Thu, Oct 30, 2003 at 01:15:44AM +0530, [EMAIL PROTECTED] wrote:
> Actually PostgreSQL is at par with MySQL when the query is being Properly
> Written(simplified)
>
> In mysql:
> mysql> SELECT DISTINCT main.* FROM Groups main join Principals Principals_1
> using(id) join ACL
> ACL_2 on
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
> Nopes the query are not Equiv , earlier one returns 4 rows and the below one
> none,
Sorry, i lowercased a string constant and dropped the lower() on email.
Try this:
SELECT *
FROM tickets
WHERE id IN (
SELECT groups.instance
The g in group had to be uppercased, the query produced the same results
but performance was worse for the IN version . 2367 ms vs 600 ms
rt3=# explain analyze SELECT * from tickets where id in ( SELECT groups.instance FROM groups
JOIN principals ON (groups.id = principals.objectid) JOIN
But the new version at lease works on 7.3 instead of putting
it in an infinite loop.
rt3=# explain analyze SELECT * from tickets where id in (
SELECT groups.instance FROM groups
rt3(# JOIN principals ON (groups.id = principals.objectid) JOIN
cachedgroupmembers ON
rt3(# (principals.id = ca
Well, you might want to try the EXISTS version. I'm not sure if it'll be
faster or slower though. In theory it should be the same.
Hum, I didn't realize the principals table was the largest table. But Postgres
knew that so one would expect it to have found a better plan. The IN/EXISTS
handling wa
Fresh import. I've been told this includes a ANALYZE.
Uh - no it doesn't.
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Does xfs_freeze work on red hat 7.3?
Cynthia Leon
-Original Message-
From: Murthy Kambhampaty [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 11:34 AM
To: 'Tom Lane'; Murthy Kambhampaty
Cc: 'Jeff'; Josh Berkus; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PRO
34 matches
Mail list logo
