When I set enable_seqscan to OFF and force everything to use the index
every stock I query returns within 100ms, but turn seqscan back ON and
its back up to taking several minutes for non-index using plans.
Any ideas?
--Stephen
Try increasing your statistics target and re-running analyze. Try say 1
Here are some results of explain analyze, I've included the LIMIT 10
because otherwise the resultset would exhaust all available memory.
explain analyze select * from history where date='2004-09-07' and
stock='ORCL' LIMIT 10;
"Limit (cost=0.00..17.92 rows=10 width=83) (actual
time=1612.000..170
Quoting Josh Berkus <[EMAIL PROTECTED]>:
> The first and most important step for RAID performance with PostgreSQL is to
>
> get a card with onboard battery back-up and enable the write cache for the
> card. You do not want to enable the write cache *without* battery back-up
>
I'm curious abo
On Thu, Sep 16, 2004 at 02:07:37PM -0700, Josh Berkus wrote:
> Jim,
>
> > What about benefits from putting WAL and pg_temp on seperate drives?
> > Specifically, we have a box with 8 drives, 2 in a mirror with the OS and
> > WAL and pg_temp; the rest in a raid10 with the database on it. Do you
> >
On Thu, 2004-09-16 at 13:39, Jim C. Nasby wrote:
> Forgive my ignorance, but I didn't think you could have a table that
> inherits from a parent not have all the columns. Or is that not what you
> mean by 'you can drop columns from the table...'?
>
> This is one advantage I see to a big UNION ALL
> My guess is that all the queries that involves the columns that are
> being indexed need to
> be rewritten to use the newly created indexes to avoid the performance
> issues. The reason
> is that REINDEX does not help either. Does it make sense?
>
Qing,
Generally, adding new indexes blindly w
Qing,
> My guess is that all the queries that involves the columns that are
> being indexed need to
> be rewritten to use the newly created indexes to avoid the performance
> issues. The reason
> is that REINDEX does not help either. Does it make sense?
What's the rate of updates on the newly in
Josh:
Sorry for the reply to the existing subject!
The newly added indexes have made all other queries much slower except
the uploading ops.
As a result, all the CPU's are running crazy but not much is getting
finished and our Application
Server waits for certain time and then times out. Customer
Jim,
> What about benefits from putting WAL and pg_temp on seperate drives?
> Specifically, we have a box with 8 drives, 2 in a mirror with the OS and
> WAL and pg_temp; the rest in a raid10 with the database on it. Do you
> think it would have been better to make one big raid10? What if it was
>
Qing,
Please don't start a new question by replying to someone else's e-mail. It
confuses people and makes it unlikely for you to get help.
> My suspicion is that since now a few indexes are added, every ops are
> run by PostgreSQL with the indexes being used when calculating cost.
> This lea
On Thu, Sep 16, 2004 at 10:50:33AM -0700, Josh Berkus wrote:
> The second step is to have lots of disks; 5 drives is a minimum for really
> good performance. 3-drive RAID5, in particular, is a poor performer for
> PostgreSQL, often resulting in I/O that is 40% or less as efficient as a
> singl
Hi, there,
I am running PostgreSQL 7.3.4 on MAC OS X G5 with dual processors and
8GB memory. The shared buffer was set as 512MB.
The database has been running great until about 10 days ago when our
developers decided to add some indexes to some tables to speed up
certain uploading ops.
Now the CPU
On Wed, Sep 15, 2004 at 02:09:31PM -0700, J. Andrew Rogers wrote:
> On Tue, 2004-09-14 at 21:30, Joe Conway wrote:
> > That's exactly what we're doing, but using inherited tables instead of a
> > union view. With inheritance, there is no need to rebuild the view each
> > time a table is added or
Josh Berkus wrote:
Primer,
The site seems to be down. I was looking forward to reading it. :(
I didn't have a problem. The site *is* in Portuguese, though.
Yes, it came up finally. Fortunately I'm Brazilian. :)
--
Daniel Ceregatti - Programmer
Omnis Network, LL
Primer,
> The site seems to be down. I was looking forward to reading it. :(
I didn't have a problem. The site *is* in Portuguese, though.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 8: explain ana
On Thu, Sep 16, 2004 at 11:10:13AM -0700, Daniel Ceregatti wrote:
> Here is a small example of the performance difference with write cache:
>
> http://sh.nu/bonnie.txt
Am I missing something here? I can't find any tests with the same machine
showing the difference between writeback and write-thro
The first part of the article can be found in
http://www.sqlmagazine.com.br/colunistas.asp?artigo=Colunistas/RicardoRezende/06_Raid_P1.asp
The site seems to be down. I was looking forward to reading it. :(
The first and most important step for RAID performance with PostgreSQL is to
get a card
Kevin Neufeld <[EMAIL PROTECTED]> writes:
> I have a relatively simple query that takes about 150ms using explain
> analyze. However, when I wrap the same query in a declared cursor
> statement, the subsequent fetch statement takes almost 30seconds. For
> some reason, the planner decided to do a
Ricardo,
Hello. I've moved your query to a more appropriate mailing list; on
PERFORMANCE we discuss RAID all the time. If you don't mind wading through a
host of opinions, you'll get plenty here. I've also cc'd our Brazillian
PostgreSQL community.
Everyone, please note that Ricardo is NOT
Christopher Kings-Lynne wrote:
Sounds like you want a many-to-many table that maps user_ids to match_ids
Then you can put an index over (user_id, match_id) and the search will
be very fast.
Chris
If I understand you correctly, I believe I've tried this approach. While
matching on a single attrib
Iain wrote:
Joe's example wasn't excluding partions, as he didn't use a predicated UNION
ALL view to select from. His queries use an indexed column that allow the
various partitions to be probed at low cost, and he was satisfied wth that.
Right.
My point in my previous post was that you could still
Hi, Steve,
On Wed, 15 Sep 2004 21:17:03 -0700
Steve Atkins <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 15, 2004 at 11:16:44AM +0200, Markus Schaber wrote:
> > But you have to add table constraints restricting the time after adding
> > the partition?
>
> Uhm... unless I'm confused that's not a meani
On 15 Sep 2004 23:55:24 -0400, Greg Stark <[EMAIL PROTECTED]> wrote:
>
> "J. Andrew Rogers" <[EMAIL PROTECTED]> writes:
>
> > We do something very similar, also using table inheritance
>
> I have a suspicion postgres's table inheritance will end up serving as a good
> base for a partitioned tabl
Hi, Mischa,
On Tue, 14 Sep 2004 22:58:20 GMT
Mischa Sandberg <[EMAIL PROTECTED]> wrote:
> Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
> and DB2's implementation of MERGE, which does what AMOUNTS to what is
> described below (one mass UPDATE...FROM, one mass INSERT...WH
Joe, Christopher,
Joe's example wasn't excluding partions, as he didn't use a predicated UNION
ALL view to select from. His queries use an indexed column that allow the
various partitions to be probed at low cost, and he was satisfied wth that.
My point in my previous post was that you could stil
Patrick Clery <[EMAIL PROTECTED]> writes:
> Here's the structure of the marital status table:
Also I find it very odd that you have a "marital status table". marital status
is just one attribute of member. Do you expect to have more than one marital
status bitfield per member? How would you dist
Patrick Clery <[EMAIL PROTECTED]> writes:
> Method 3 is the only one that used the index, but the only really acceptable
> method here is Method 1.
>
> My questions are...
> - Is there any hope in getting this to use an efficient index?
> - Any mathmaticians know if there is a way to reorder my
Sounds like you want a many-to-many table that maps user_ids to match_ids
Then you can put an index over (user_id, match_id) and the search will
be very fast.
Chris
Patrick Clery wrote:
I'm working on a dating/personals/match-making site, that has used many
different methods of "match-making", t
I'm working on a dating/personals/match-making site, that has used many
different methods of "match-making", that all seem to be very slow. One I am
attempting now that seems to be an efficient method of storage, but not the
best for indexing, is using bitwise operators to compare one person's p
29 matches
Mail list logo
