Oleg,
> All right, my query just finished running with EXPLAIN ANALYZE.
> I show the plan below and also attached it as a file.
> Any ideas?
Yes. Your problem appears to be right here:
>-> Nested Loop
> (cost=0.00..54596.00 rows=3 width=88) (actual time=
Oleg Lebedev <[EMAIL PROTECTED]> writes:
> All right, my query just finished running with EXPLAIN ANALYZE.
> I show the plan below and also attached it as a file.
> Any ideas?
Uh, have you done an ANALYZE (or VACUUM ANALYZE) on this database?
It sure looks like the planner thinks the tables are a
For troubleshooting, can you try it with "set enable_nestloop = false" and
rerun the query and see how long it takes?
It looks like the estimates of rows returned is WAY off (estimate is too
low compared to what really comes back.)
Also, you might try to alter the table.column to have a highe
All right, my query just finished running with EXPLAIN ANALYZE.
I show the plan below and also attached it as a file.
Any ideas?
-> Sort (cost=54597.49..54597.50 rows=1 width=121) (actual
time=6674562.03..6674562.15 rows=175 loops=1)
Sort Key: nation.n_name, date_part('year'::text,
o
On Wed, 1 Oct 2003 19:45:29 +0200 (MEST), "Dimitri Nagiev"
<[EMAIL PROTECTED]> wrote:
>template1=# explain analyze select * from mytable where
>mydate>='2003-09-01';
> Seq Scan on mytable (cost=0.00..2209.11 rows=22274 width=562) (actual
> time=0.06..267.30 rows=22677 loops=1)
> Filter: (mydate
Oh, to followup on my previously sent post, make sure you've got
effective_cache_size set right BEFORE you go trying to set
random_page_cost, and you might wanna run a select * from table to load
the table into kernel buffer cache before testing, then also test it with
the cache cleared out (s
On Wed, 1 Oct 2003, Dimitri Nagiev wrote:
> here goes the EXPLAIN ANALYZE output:
>
>
> template1=# VACUUM analyze mytable;
> VACUUM
> template1=# explain analyze select * from mytable where
> mydate>='2003-09-01';
> QUERY PLAN
>
On Wed, 2003-10-01 at 13:45, Dimitri Nagiev wrote:
> template1=# explain analyze select * from mytable where
> mydate>='2003-09-01';
> QUERY PLAN
>
>
>
Sure, below is the query. I attached the plan to this posting.
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
Oleg,
> The output of the query should contain about 200 rows. So, I guess the
> planer is off assuming that the query should return 1 row.
Oh, also did you post the query before? Can you re-post it with the planner
results?
--
Josh Berkus
Aglio Database Solutions
San Francisco
That would be great! When do you think this would be ready for us to see
;?)
-Original Message-
From: Jeff [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 11:42 AM
To: Oleg Lebedev
Cc: [EMAIL PROTECTED]
Subject: RE: [PERFORM] Tuning/performance issue...
On Wed, 1 Oct 2003, O
here goes the EXPLAIN ANALYZE output:
template1=# VACUUM analyze mytable;
VACUUM
template1=# explain analyze select * from mytable where
mydate>='2003-09-01';
QUERY PLAN
Jason,
Your question is really suited to the PERFORMANCE list, not NOVICE, so I have
cross-posted it there. I reccomend that you subscribe to performance, and
drop novice from your replies. There are lots of hardware geeks on
performance, but few on novice.
> We have an opportunity to purch
On Wed, 1 Oct 2003, Oleg Lebedev wrote:
> Jeff,
> I would really appreciate if you could send me that lengthy presentation
> that you've written on pg/other dbs comparison.
> Thanks.
>
After I give the presentation at work and collect comments from my
coworkers (and remove some information you fo
On Wed, 2003-10-01 at 13:30, Dimitri Nagiev wrote:
> Hi all,
>
> I haven't found any official documentation about the postgres sql optimizer
> on the web, so please forgive me if there is such a document and point me to
> the right direction.
>
> I've got the following problem: I cannot make the
The output of the query should contain about 200 rows. So, I guess the
planer is off assuming that the query should return 1 row.
I will start EXPLAIN ANALYZE now.
Thanks.
Oleg
-Original Message-
From: scott.marlowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 7:23 AM
To
Hi all,
I haven't found any official documentation about the postgres sql optimizer
on the web, so please forgive me if there is such a document and point me to
the right direction.
I've got the following problem: I cannot make the postgres SQL Optimizer use
an index on a date field to filter out
[EMAIL PROTECTED] (Andrew Sullivan) writes:
> On Wed, Oct 01, 2003 at 07:14:32AM -0600, scott.marlowe wrote:
>> FYI, on a Dual PIV2800 with 2 gig ram and a single UDMA 80 gig hard drive,
>> I from 420 tps to 22 tps when I disable write caching. WOW. A factor of
>> about 20 times slower. (pgben
[EMAIL PROTECTED] writes:
> So.. does anybody have any advice?
Look at set_inherited_rel_pathlist() in allpaths.c --- it forms the best
plan for fully scanning the inheritance-tree table. Currently that's
the *only* plan considered, and it does not make any use of join
clauses. It's possible tha
Oleg,
> I continue struggling with the TPC-R benchmarks and wonder if anyone
> could help me optimize the query below. ANALYZE statistics indicate that
> the query should run relatively fast, but it takes hours to complete. I
> attached the query plan to this posting.
Even though it takes hours t
Tom Lane wrote:
> When benchmarking with data sets considerably larger than available
> buffer cache, I rather doubt that small random_page_cost would be a
> good idea. Still, you might as well experiment to see.
>From experience, I know the difference in response time can be huge when postgres
Jeff,
I would really appreciate if you could send me that lengthy presentation
that you've written on pg/other dbs comparison.
Thanks.
Oleg
-Original Message-
From: Jeff [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 6:23 AM
To: David Griffiths
Cc: [EMAIL PROTECTED]
Subject:
Hi,
In some situations, it looks like the optimizer does not chose
efficient paths for joining inherited tables. For I created a
rather trivial formulation to serve as an example. I created the table
'numbers' comprising of the columns id (int) and value (text). I also
created the ta
David Griffiths <[EMAIL PROTECTED]> writes:
>> The reason PG's planner doesn't discover this join order for itself
>> is that it's written to not attempt to re-order outer joins from the
>> syntactically defined ordering. In general, such reordering would
>> change the results. It is possible to
On Tue, 30 Sep 2003, Oleg Lebedev wrote:
> I continue struggling with the TPC-R benchmarks and wonder if anyone
> could help me optimize the query below. ANALYZE statistics indicate that
> the query should run relatively fast, but it takes hours to complete. I
> attached the query plan to this pos
On Wed, Oct 01, 2003 at 07:14:32AM -0600, scott.marlowe wrote:
> FYI, on a Dual PIV2800 with 2 gig ram and a single UDMA 80 gig hard drive,
> I from 420 tps to 22 tps when I disable write caching. WOW. A factor of
> about 20 times slower. (pgbench -c 4 -t 100)
That's completely consistent wit
On Wed, 1 Oct 2003, Andrew Sullivan wrote:
> On Wed, Sep 17, 2003 at 04:46:00PM -0400, Michael Adler wrote:
> > So the quesiton is whether it is ever sensible to use write-caching and
> > expect comparable persistence.
>
> Yes. If and only if you have a battery-backed cache. I know of no
> IDE
> > While writing web application I found that it would
> > be very nice for me to have "null" WHERE clause. Like
> > WHERE 1=1. Then it is easy to concat additional
> > conditions just using $query . " AND col=false" syntax.
> >
> > But which of the possible "null" clauses is the fastest
> > o
On Wed, 2003-10-01 at 08:11, Mindaugas Riauba wrote:
> While writing web application I found that it would
> be very nice for me to have "null" WHERE clause. Like
> WHERE 1=1. Then it is easy to concat additional
> conditions just using $query . " AND col=false" syntax.
>
> But which of the poss
On Wednesday 01 October 2003 13:11, Mindaugas Riauba wrote:
> Hello,
>
> While writing web application I found that it would
> be very nice for me to have "null" WHERE clause. Like
> WHERE 1=1. Then it is easy to concat additional
> conditions just using $query . " AND col=false" syntax.
>
>
Mindaugas Riauba wrote:
Hello,
While writing web application I found that it would
be very nice for me to have "null" WHERE clause. Like
WHERE 1=1. Then it is easy to concat additional
conditions just using $query . " AND col=false" syntax.
But which of the possible "null" clauses is the fa
On Tue, 30 Sep 2003, David Griffiths wrote:
>
> This is all part of a "migrate away from Oracle" project. We are looking at
> 3 databases -
> MySQL (InnoDB), Postgres and Matisse (object oriented). We have alot of
> queries like this
> or worse, and I'm worried that many of them would need to be r
Hello,
While writing web application I found that it would
be very nice for me to have "null" WHERE clause. Like
WHERE 1=1. Then it is easy to concat additional
conditions just using $query . " AND col=false" syntax.
But which of the possible "null" clauses is the fastest
one?
Thanks,
On Wed, Sep 17, 2003 at 04:46:00PM -0400, Michael Adler wrote:
> So the quesiton is whether it is ever sensible to use write-caching and
> expect comparable persistence.
Yes. If and only if you have a battery-backed cache. I know of no
IDE drives that have that, but there's nothing about the spe
David Griffiths wrote:
And finally,
Here's the contents of the postgresql.conf file (I've been playing with
these setting the last couple of days, and using the guide @
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html to
make sure I didn't have it mis-tuned):
tcpip_s
35 matches
Mail list logo
