Why would postgres use a different query plan for declared cursors than
without?
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
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
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
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
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
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...WHERE
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 table feature. Is
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 meaningful
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
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
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
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
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
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
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
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,
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
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 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
single
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 leads
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
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.
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
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 will
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
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
think it
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
27 matches
Mail list logo
