> Yeah.. I came across pgtune but noticed that latest version dated 2009-10-29
> http://pgfoundry.org/frs/?group_id=1000416 which is kind of outdated. Tar
> file has settings for pg 8.3. Is still relevant?
>
> Yes, I'm sure it will not do anything bad to your config.
>
Apologies for leaping
On Monday, January 28, 2013, Alex Vinnik wrote:
> It sure turned out that default settings are not a good fit. Setting
> random_page_cost
> to 1.0 made query to run in 2.6 seconds and I clearly see that indexes are
> being used in explain plan and IO utilization is close to 0.
>
This is not surp
On Mon, Jan 28, 2013 at 4:55 PM, Filip Rembiałkowski
wrote:
>
> On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik wrote:
>>
>> It sure turned out that default settings are not a good fit.
>
>
> do you know pgtune?
> it's a good tool for starters, if you want a fast postgres and don't really
> want to
index definition
CREATE INDEX views_visit_id_visit_buoy_index ON views USING btree
(visit_id, visit_buoy)
On Tue, Jan 29, 2013 at 1:35 PM, Merlin Moncure wrote:
> On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik
> wrote:
> >
> >
> >
> > On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot
> wrote:
> >>
On Tue, Jan 29, 2013 at 2:06 PM, Jeff Janes wrote:
> > Sort Key: visits.id, views.id
> > Sort Method: external sort Disk: 4248kB
>
> What query are you running? The query you originally showed us should
> not be doing this sort in the first place.
>
> Cheers,
>
> Jeff
>
Here is the query
On Mon, Jan 28, 2013 at 3:43 PM, Alex Vinnik wrote:
> It sure turned out that default settings are not a good fit. Setting
> random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly see
> that indexes are being used in explain plan and IO utilization is close to
> 0.
>
> QUERY PLAN
>
On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik wrote:
>
>
>
> On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot wrote:
>>
>> On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
>>
>>> random_page_cost=1 might be not what you really want.
>>> it would mean that random reads are as fast as as sequential read
On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot wrote:
> On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
>
> random_page_cost=1 might be not what you really want.
>> it would mean that random reads are as fast as as sequential reads, which
>> probably is true only for SSD
>>
> What randon_page_cost
On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
> random_page_cost=1 might be not what you really want.
> it would mean that random reads are as fast as as sequential reads, which
> probably is true only for SSD
> What randon_page_cost would be more appropriate for EC2 EBS Provisioned
> volume
On Tue, Jan 29, 2013 at 8:41 AM, Alex Vinnik wrote:
> Setting work_mem to 64MB triggers in memory sort but look what happens with
> views look up. PG goes through all records there "Seq Scan on views" instead
> of using visitor_id index and I have only subset of real data to play
> around. Can ima
On Tue, Jan 29, 2013 at 8:24 AM, Alex Vinnik wrote:
> On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski
> wrote:
>
>>
>> do you know pgtune?
>> it's a good tool for starters, if you want a fast postgres and don't
>> really want to learn what's behind the scenes.
>>
> Yeah.. I came across pgtu
Setting work_mem to 64MB triggers in memory sort but look what happens with
views look up. PG goes through all records there "Seq Scan on views"
instead of using visitor_id index and I have only subset of real data to
play around. Can imagine what cost would be running it against bigger
dataset. So
On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski wrote:
>
> On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik wrote:
>
>> It sure turned out that default settings are not a good fit.
>>
>
> do you know pgtune?
> it's a good tool for starters, if you want a fast postgres and don't
> really want to
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik wrote:
> It sure turned out that default settings are not a good fit. Setting
> random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly see
> that indexes are being used in explain plan and IO utilization is close to
> 0.
>
> QUERY PLAN
>
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik wrote:
> It sure turned out that default settings are not a good fit.
>
do you know pgtune?
it's a good tool for starters, if you want a fast postgres and don't really
want to learn what's behind the scenes.
random_page_cost=1 might be not what you r
It sure turned out that default settings are not a good fit. Setting
random_page_cost
to 1.0 made query to run in 2.6 seconds and I clearly see that indexes are
being used in explain plan and IO utilization is close to 0.
QUERY PLAN
Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual
tim
On Wed, Jan 9, 2013 at 9:49 AM, Alex Vinnik wrote:
> Guys, thanks a lot for your input. It is very valuable for us. We plan to
> fix a separate dev server similar to production one, copy all data there and
> try you suggestions as we really don't want to do it on production server. I
> also notice
Guys, thanks a lot for your input. It is very valuable for us. We plan to
fix a separate dev server similar to production one, copy all data there
and try you suggestions as we really don't want to do it on production
server. I also noticed that IOPS jumps to 100% when running this query. So
it is
On Thursday, January 3, 2013, Alex Vinnik wrote:
> Hi everybody,
>
> I have implemented my first app using PG DB and thought for a minute(may
> be two) that I know something about PG but below
> problem totally destroyed my confidence :). Please help me to restore it.
>
> Here is simple join query
On Thu, Jan 3, 2013 at 4:54 PM, Alex Vinnik wrote:
> Don't understand why PG doesn't use views_visit_id_index in that query but
> rather scans whole table. One explanation I have found that when resulting
> dataset constitutes ~15% of total number of rows in the table then seq scan
> is used. In t
On 01/03/2013 11:54 PM, Alex Vinnik wrote:
Don't understand why PG doesn't use views_visit_id_index in that query
but rather scans whole table. One explanation I have found that when
resulting dataset constitutes ~15% of total number of rows in the table
then seq scan is used. In this case result
On 01/03/2013 10:54 PM, Alex Vinnik wrote:
I have implemented my first app using PG DB and thought for a minute(may be
two) that I know something about PG but below problem totally destroyed my
confidence :). Please help me to restore it.
https://wiki.postgresql.org/wiki/SlowQueryQuestions
--
J
Hi everybody,
I have implemented my first app using PG DB and thought for a minute(may be
two) that I know something about PG but below problem totally destroyed my
confidence :). Please help me to restore it.
Here is simple join query. It runs just fine on MS SQL 2008 and uses
all available inde
I run on PostgreSQL 8.3, default settings (also tried to change
random_page_cost close to 1).
What need I change to make the second query run as fast as the first?
Set enable_hashjoin to off solves this problem, but it's not the way I
can use.
Statistics for all columns is on the level 1000.
e
Jim C. Nasby wrote:
Ok, now that I've actually looked at the release notes, I take that back
and apologize. But while there's a lot of improvements that have been
made, there's still some seriously tough problems that have been talked
about for a long time and there's still no "light at the end
Tom Lane wrote:
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
If someone's going to commit to putting effort into improving the
planner then that's wonderful. But I can't recall any significant
planner improvements since min/max (which I'd argue was more of a bug
fix than an improvement).
Hmph.
On Thu, 2006-10-12 at 09:44, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > If someone's going to commit to putting effort into improving the
> > planner then that's wonderful. But I can't recall any significant
> > planner improvements since min/max (which I'd argue was more of a
On Thu, Oct 12, 2006 at 10:44:20AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > If someone's going to commit to putting effort into improving the
> > planner then that's wonderful. But I can't recall any significant
> > planner improvements since min/max (which I'd argue
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> If someone's going to commit to putting effort into improving the
> planner then that's wonderful. But I can't recall any significant
> planner improvements since min/max (which I'd argue was more of a bug
> fix than an improvement).
Hmph. Apparently I
On Thu, Oct 12, 2006 at 10:59:23PM +1300, Mark Kirkwood wrote:
> H.J. Sanders wrote:
>
> > why not just like in some other (commercial) databases:
> >
> > a statement to say: use index
> >
> > I know this is against all though but if even the big ones can not resist
> > the pressure
H.J. Sanders wrote:
why not just like in some other (commercial) databases:
a statement to say: use index
I know this is against all though but if even the big ones can not resist
the pressure of their users, why not?
Yeah - some could not (e.g. Oracle), but some did (e
Sanders
> > -Oorspronkelijk bericht-
> > Van: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] Bucky Jordan
> > Verzonden: woensdag 11 oktober 2006 16:27
> > Aan: Tom Lane; Brian Herlihy
> > CC: Postgresql Performance
> > Onderwerp:
On Wed, Oct 11, 2006 at 10:27:26AM -0400, Bucky Jordan wrote:
> Also, I'm guessing this has already come up at some point, but what
> about allowing PG to do some stat collection during queries? If you're
> touching a lot of data (such as an import process) wouldn't it be more
> efficient (and perh
Tom,
I'm interested in the problem of cross-column statistics from a
theoretical perspective. It would be interesting to sit down and try to
reason out a useful solution, or at very least to understand the problem
better so I can anticipate when it might come and eat me.
>From my understanding,
Heikki Linnakangas wrote:
> BTW, in DB2 you can declare a table as volatile, which means that the
> cardinality of the table varies greatly. The planner favors index scans
> on volatile tables.
Now that seems like a valuable idea.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp:/
Bucky Jordan wrote:
Is this along the lines of "I'm loading a big table and touching every
row of data, so I may as well collect some stats along the way" and "I
know my data contains these statistical properties, but the analyzer
wasn't able to figure that out (or maybe can't figure it out effi
> Brian Herlihy <[EMAIL PROTECTED]> writes:
> > What would it take for hints to be added to postgres?
>
> A *whole lot* more thought and effort than has been expended on the
> subject to date.
>
> Personally I have no use for the idea of "force the planner to do
> exactly X given a query of exact
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Personally I have no use for the idea of "force the planner to do
> exactly X given a query of exactly Y". You don't have exactly Y
> today, tomorrow, and the day after (if you do, you don't need a
> hint mechanism at all, you need a mysql-style query cach
Brian Herlihy <[EMAIL PROTECTED]> writes:
> What would it take for hints to be added to postgres?
A *whole lot* more thought and effort than has been expended on the
subject to date.
Personally I have no use for the idea of "force the planner to do
exactly X given a query of exactly Y". You don'
-- tom lane wrote -
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I'd rather have the ugly solution sooner rather than the elegant one
> later (if ever).
The trouble with that is that we couldn't ever get rid of it, and we'd
be stuck with back
Mark Kirkwood wrote:
who believe it is possible to build a "start enough" optimizer.
That's meant to read "smart enough" optimizer .. sorry.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Jim C. Nasby wrote:
(snippage)... but we'll never get any progress so long as every
time hints are brought up the response is that they're evil and should
never be in the database. I'll also say that a very simple hinting
language (ie: allowing you to specify access method for a table, and
join
On Tue, Oct 10, 2006 at 10:28:29AM -0700, Josh Berkus wrote:
> Jim,
>
> > We've depricated things before, I'm sure we'll do it again. Yes, it's a
> > pain, but it's better than not having anything release after release.
> > And having a formal hint language would at least allow us to eventually
>
Jim,
> We've depricated things before, I'm sure we'll do it again. Yes, it's a
> pain, but it's better than not having anything release after release.
> And having a formal hint language would at least allow us to eventually
> clean up some of these oddball cases, like the OFFSET 0 hack.
>
> I'm a
On Mon, Oct 09, 2006 at 23:33:03 +0200,
Tobias Brox <[EMAIL PROTECTED]> wrote:
>
> Just a comment from the side line; can't the rough "set
> enable_seqscan=off" be considered as sort of a hint anyway? There have
> been situations where we've actually had to resort to such crud.
That only works
Steinar H. Gunderson wrote:
> On Tue, Oct 10, 2006 at 09:07:03AM -0500, Jim C. Nasby wrote:
>> Would you put something from the obfuscated C contest
>> into production with comments describing what it does,
>
> If nothing else, it would be a nice practical joke =)
nice isn't the word I would use
Jim C. Nasby wrote:
> On Mon, Oct 09, 2006 at 08:22:39PM -0700, Joshua D. Drake wrote:
>>> Imagine I got run over by a train, and someone was reading my code.
>>> Which would be easier for them to maintain: Code with weird SQL, or code
>>> with sensible, well-written SQL and explicit hints?
>> You
Jim C. Nasby wrote:
> On Mon, Oct 09, 2006 at 03:41:09PM -0700, Joshua D. Drake wrote:
>>> One of the big problems with doing set enable_...=off is that there's no
>>> way to embed that into something like a view, so you're almost forced
>>> into putting into the application code itself, which make
On Tue, Oct 10, 2006 at 10:14:48AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > I'd rather have the ugly solution sooner rather than the elegant one
> > later (if ever).
>
> The trouble with that is that we couldn't ever get rid of it, and we'd
> be stuck with backward-c
On Tue, Oct 10, 2006 at 09:07:03AM -0500, Jim C. Nasby wrote:
> Would you put something from the obfuscated C contest
> into production with comments describing what it does,
If nothing else, it would be a nice practical joke =)
/* Steinar */
--
Homepage: http://www.sesse.net/
-
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I'd rather have the ugly solution sooner rather than the elegant one
> later (if ever).
The trouble with that is that we couldn't ever get rid of it, and we'd
be stuck with backward-compatibility concerns with the first (over
simplified) design. It's i
On Mon, Oct 09, 2006 at 08:22:39PM -0700, Joshua D. Drake wrote:
>
> > Imagine I got run over by a train, and someone was reading my code.
> > Which would be easier for them to maintain: Code with weird SQL, or code
> > with sensible, well-written SQL and explicit hints?
>
> You forgot the most
On Mon, Oct 09, 2006 at 03:41:09PM -0700, Joshua D. Drake wrote:
> >
> > One of the big problems with doing set enable_...=off is that there's no
> > way to embed that into something like a view, so you're almost forced
> > into putting into the application code itself, which makes matters even
>
On Mon, Oct 09, 2006 at 06:45:16PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > One of the big problems with doing set enable_...=off is that there's no
> > way to embed that into something like a view, so you're almost forced
> > into putting into the application code it
> Imagine I got run over by a train, and someone was reading my code.
> Which would be easier for them to maintain: Code with weird SQL, or code
> with sensible, well-written SQL and explicit hints?
You forgot the most important option:
Code with appropriate documentation about your weird SQL.
Brian Herlihy wrote:
PG does support hints actually..
The only thing is, the hints are expressed in an obscure, ad-hoc and
implementation dependant language.
For example, the "Don't use index X" hint (the one I used) can be accessed by
replacing your index with an index on values derived from t
PG does support hints actually.. and I used them to solve the last performance
problem I had, rather than waiting n years for the query planner to be
improved. The problem in question (from an automated query planning point of
view) is the lack of multi-column statistics, leading to the wrong inde
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> One of the big problems with doing set enable_...=off is that there's no
> way to embed that into something like a view, so you're almost forced
> into putting into the application code itself, which makes matters even
> worse. If you could hint this wit
>
> One of the big problems with doing set enable_...=off is that there's no
> way to embed that into something like a view, so you're almost forced
> into putting into the application code itself, which makes matters even
> worse. If you could hint this within a query (maybe even on a per-table
>
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I'll also say that a very simple hinting
> language (ie: allowing you to specify access method for a table, and
> join methods) would go a huge way towards enabling app developers to get
> stuff done now while waiting for all these magical optimizer
> im
On Mon, Oct 09, 2006 at 11:33:03PM +0200, Tobias Brox wrote:
> [Jim C. Nasby - Mon at 04:18:27PM -0500]
> > I can agree to that, but we'll never get any progress so long as every
> > time hints are brought up the response is that they're evil and should
> > never be in the database. I'll also say t
[Jim C. Nasby - Mon at 04:18:27PM -0500]
> I can agree to that, but we'll never get any progress so long as every
> time hints are brought up the response is that they're evil and should
> never be in the database. I'll also say that a very simple hinting
> language (ie: allowing you to specify acc
On Mon, Oct 09, 2006 at 06:07:29PM +, Chris Browne wrote:
> [EMAIL PROTECTED] ("Craig A. James") writes:
> > Mark Kirkwood wrote:
> >>> The result? I can't use my function in any WHERE clause that
> >>> involves any other conditions or joins. Only by itself. PG will
> >>> occasionally decide
On Sun, 2006-10-08 at 18:05, Josh Berkus wrote:
> Now, if you were offering us a patch to auto-populate the statistics as a
> table is loaded, I'd be all for that. But I, personally, would need a
> lot of convincing to believe that hints don't do more harm than good.
Actually, I'd much rather
[EMAIL PROTECTED] ("Craig A. James") writes:
> Mark Kirkwood wrote:
>>> The result? I can't use my function in any WHERE clause that
>>> involves any other conditions or joins. Only by itself. PG will
>>> occasionally decide to use my function as a filter instead of doing
>>> the join or the oth
Josh Berkus writes:
> Unfortunately, EDB's solution is likely to be Oracle-based, which is
> liable to fall into the trap of "not good enough."
I'd be a bit worried about Oracle patents as well...
regards, tom lane
---(end of broadcast)---
Tom,
> Josh's post points out some reasons why it's not that easy to get
> long-term benefits from hints --- you could possibly address some of
> those problems, but a hint language that responds to those criticisms
> won't be trivial to design, implement, or maintain. See (many) past
> discussio
Mark Kirkwood <[EMAIL PROTECTED]> writes:
> True enough - but (aside from the fact that hints might take just as
> long to get into the development tree as cost-for-functions might take
> to write and put in...) there is a nasty side effect to adding hints -
> most of the raw material for optimi
Craig A. James wrote:
Perhaps you scanned past what I wrote a couple paragraphs farther down.
I'm going to repeat it because it's the KEY POINT I'm trying to make:
Craig James wrote:
Now you might argue that function-cost needs to be added to the
optimizer's arsenal of tricks. And I'd agr
[EMAIL PROTECTED] (Tom Lane) writes:
> Another thing we've been beat up about in the past is that loading a
> pg_dump script doesn't ANALYZE the data afterward...
Do I misrecall, or were there not plans (circa 7.4...) to for pg_dump
to have an option to do an ANALYZE at the end?
I seem to remembe
Mark Kirkwood wrote:
The result? I can't use my function in any WHERE clause that involves
any other conditions or joins. Only by itself. PG will occasionally
decide to use my function as a filter instead of doing the join or the
other WHERE conditions first, and I'm dead.
this is an argum
Bruce Momjian wrote:
I can do 100! on my computer, but can't do it in my head.
A poor example. 100! is a simple repetative calculation, something computers
are very good at. Optimizing an SQL query is very difficult, and a completely
different class of problem.
The fact is the PG team has
Craig A. James wrote:
My example, discussed previously in this forum, is a classic. I have a
VERY expensive function (it's in the class of NP-complete problems, so
there is no faster way to do it). There is no circumstance when my
function should be used as a filter, and no circumstance wh
Jonah H. Harris wrote:
> On Oct 08, 2006 07:05 PM, Josh Berkus wrote:
> > Hints are used because the DBA thinks that they are smarter than
> > the optimizer; 99% of the time, they are wrong.
>
> That's a figure which I'm 100% sure cannot be backed up by fact.
>
> > Just try manually optimizing a
Josh Berkus writes:
> Now, if you were offering us a patch to auto-populate the statistics as a
> table is loaded, I'd be all for that.
Curiously enough, I was just thinking about that after reading Craig's
post. autovacuum will do this, sort of, if it's turned on --- but its
reaction time is m
... and add 100 other problems. Hints are used because the DBA thinks that
they are smarter than the optimizer; 99% of the time, they are wrong.
Just try manually optimizing a complex query, you'll see -- with three
join types, several scan types, aggregates, bitmaps, internal and external
so
Denis,
> Wouldn't PG supporting simple optmizer hints get around this kinda
> problem? Seems to me that at least one customer posting per week
> would be solved via the use of simple hints.
... and add 100 other problems. Hints are used because the DBA thinks that
they are smarter than the op
On Oct 7, 2006, at 8:50 PM, Denis Lussier wrote:
Wouldn't PG supporting simple optmizer hints get around this kinda
problem? Seems to me that at least one customer posting per week
would be solved via the use of simple hints.
If the community is interested... EnterpriseDB has added support fo
Wouldn't PG supporting simple optmizer hints get around this kinda
problem? Seems to me that at least one customer posting per week
would be solved via the use of simple hints.
If the community is interested... EnterpriseDB has added support for
a few different simple types of hints (optimize
"Craig A. James" <[EMAIL PROTECTED]> writes:
> There are two plans below. The first is before an ANALYZE HITLIST_ROWS, and
> it's horrible -- it looks to me like it's sorting the 16 million rows of the
> SEARCH table. Then I run ANALYZE HITLIST_ROWS, and the plan is pretty decent.
It would be
I have two tables, SAMPLE and HITLIST that when joined, generate a monsterous
sort.
HITLIST_ROWS has about 48,000 rows
SAMPLE has about 16 million rows
The joined column is indexed in SAMPLE
HITLIST_ROWS is a scratch table which is used a few times then discarded.
HITLIST_ROWS has no index
On Wed, Mar 29, 2006 at 01:08:15AM -0500, stef wrote:
>
> If your looking for suggestions, I would suggest updating the 8.1.x you
> have installed to the latest version, as of typing this is 8.1.3 ;) Most
> notable is some of the -bug- fixes that are in since 8.1.0, for example;
>
> * Fix inco
On Wed, 29 Mar 2006 01:08:15 -0500
stef <[EMAIL PROTECTED]> threw this fish to the penguins:
>
> If your looking for suggestions, I would suggest updating the 8.1.x you
> have installed to the latest version, as of typing this is 8.1.3 ;) Most
> notable is some of the -bug- fixes that are in s
If your looking for suggestions, I would suggest updating the 8.1.x you
have installed to the latest version, as of typing this is 8.1.3 ;) Most
notable is some of the -bug- fixes that are in since 8.1.0, for example;
* Fix incorrect optimizations of outer-join conditions (Tom)
You know, mi
george young wrote:
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I have a simple join on two tables that takes way too long. Can you help
me understand what's wrong? There are indexes defined on the relevant columns.
I just did a fresh vacuum --full --analyze on the
> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:pgsql-performance-
> [EMAIL PROTECTED] On Behalf Of Steinar H. Gunderson
> A merge join requires sorted inputs.
>
> > Most of the time was spent sorting the parameters parameters table
by
> > opset_num even though opset_num is indexed
On Tue, 28 Mar 2006 19:17:49 +0100
Simon Riggs <[EMAIL PROTECTED]> threw this fish to the penguins:
> On Tue, 2006-03-28 at 10:22 -0500, george young wrote:
>
> > work_mem= 1024
>
> Set that higher.
>
> Try a couple of other plans using enable_* and let us have the EXPLAIN
> ANALYZE plans.
I tr
On Tue, 2006-03-28 at 10:22 -0500, george young wrote:
> work_mem= 1024
Set that higher.
Try a couple of other plans using enable_* and let us have the EXPLAIN
ANALYZE plans.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: don't forget
On Tue, Mar 28, 2006 at 11:20:19AM -0600, Dave Dutcher wrote:
> I guess what I am really curious about is why was the OP getting an
> expensive sort when the planner tried a merge join?
A merge join requires sorted inputs.
> Most of the time was spent sorting the parameters parameters table by
>
On Tue, Mar 28, 2006 at 06:29:08PM +0200, Steinar H. Gunderson wrote:
> On Tue, Mar 28, 2006 at 10:18:25AM -0600, Dave Dutcher wrote:
> >> "parameters_idx" btree (run, opset_num, step_num, opset,
> > opset_ver,
> >> step, step_ver, name, split, wafers)
> >> "parameters_opset_idx" btree (ops
> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:pgsql-performance-
> [EMAIL PROTECTED] On Behalf Of Steinar H. Gunderson
> Sent: Tuesday, March 28, 2006 10:29 AM
>
> An index on (A,B,C) can be used for a query on (A,B) or (A), so it
doesn't
> really matter. It isn't usable for a que
On Tue, Mar 28, 2006 at 10:18:25AM -0600, Dave Dutcher wrote:
>> "parameters_idx" btree (run, opset_num, step_num, opset,
> opset_ver,
>> step, step_ver, name, split, wafers)
>> "parameters_opset_idx" btree (opset, step, name)
>> "parameters_step_idx" btree (step, name)
> Have you tried
> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:pgsql-performance-
> [EMAIL PROTECTED] On Behalf Of george young
> Sent: Monday, March 27, 2006 12:48 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] simple join uses indexes, very slow
>
Hi, George,
george young wrote:
>>Looks like a hash join might be faster. What is your work_mem set to?
>
> work_mem= 1024
This is 1 Megabyte. By all means, increase it, if possible.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Developme
On Tue, 28 Mar 2006 09:30:54 +0100
Simon Riggs <[EMAIL PROTECTED]> threw this fish to the penguins:
> On Mon, 2006-03-27 at 13:47 -0500, george young wrote:
>
> > Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745 tuples.
> >
> > explain analyze SELECT ro.run, ro.opset_num, p.s
On Mon, 2006-03-27 at 13:47 -0500, george young wrote:
> Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745 tuples.
>
> explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM run_opsets ro,
> parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num and
> ro.run='
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I have a simple join on two tables that takes way too long. Can you help
me understand what's wrong? There are indexes defined on the relevant columns.
I just did a fresh vacuum --full --analyze on the two tables.
Is there s
Mark Kirkwood wrote:
Kevin Brown wrote:
I'll just start by warning that I'm new-ish to postgresql.
I'm running 8.1 installed from source on a Debian Sarge server. I
have a simple query that I believe I've placed the indexes correctly
for, and I still end up with a seq scan. It makes sense,
On Wed, 2005-12-14 at 17:47 -0500, Tom Lane wrote:
> That plan looks perfectly fine to me. You could try forcing some other
> choices by fooling with the planner enable switches (eg set
> enable_seqscan = off) but I doubt you'll find much improvement. There
> are too many rows being pulled from o
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > I'm running 8.1 installed from source on a Debian Sarge server. I have a
> > simple query that I believe I've placed the indexes correctly for, and I
> > still end up with a seq scan. It makes sense, kinda, but it should be able
> >
1 - 100 of 116 matches
Mail list logo
