Please reply-all so others can learn and contribute.
On Sun, Jul 29, 2007 at 09:38:12PM -0700, Craig James wrote:
Decibel! wrote:
It's unlikely that it's going to be faster to index scan 2.3M rows than
to sequential scan them. Try setting enable_seqscan=false and see if it
is or not.
Out
Jay Kang wrote:
Hello,
I'm currently trying to decide on a database design for tags in my web
2.0application. The problem I'm facing is that I have 3 separate
tables
i.e. cars, planes, and schools. All three tables need to interact with the
tags, so there will only be one universal set of tags
Thanks for the reply Richard, but I guess I didn't explain myself well. I
have three tables that needs to be mapped to the Tags table. Most of the web
references that I mentioned only maps one table to the Tags table. Here is
my Tags table:
CREATE TABLE Tags
(
TagID serial NOT NULL,
TagName
Jay Kang wrote:
Thanks for the reply Richard, but I guess I didn't explain myself well. I
have three tables that needs to be mapped to the Tags table. Most of the web
references that I mentioned only maps one table to the Tags table. Here is
my Tags table:
One quick point. SQL is
Richard Huxton wrote:
CREATE TABLE car_tags
(
CarID integer NOT NULL,
TagID integer NOT NULL
);
[snip other table defs]
Don't forget CarID isn't really an integer (I mean, you're not going to
be doing sums with car id's are you?) it's actually just a unique code.
Of course, computers
Hey Richard,
Thanks again for the reply, its great to hear some feedback. So once again,
here we go:
On 7/30/07, Richard Huxton [EMAIL PROTECTED] wrote:
Jay Kang wrote:
Thanks for the reply Richard, but I guess I didn't explain myself well.
I
have three tables that needs to be mapped to
Jay Kang wrote:
One quick point. SQL is case-insensitive unless you double-quote
identifiers. This means CamelCase tend not to be used. So instead of
AddedBy you'd more commonly see added_by.
Yes, I am aware that postgre is case-insensitive, but I write all query with
case so its easier for me
Jay Kang wrote:
Hey Richard,
Sorry for the late reply, I was just making my first test version of the DB
closely resembling you suggested design. Just wanted to write you back
answering your questions. So here we go:
No problem - it's email and what with different timezones it's common to
On 7/28/07, Jim C. Nasby [EMAIL PROTECTED] wrote:
What are your vacuum_cost_* settings? If you set those too aggressively
you'll be in big trouble.
autovacuum_vacuum_cost_delay = 100
autovacuum_vacuum_cost_limit = 200
These are generally fine, autovacuum keeps up, and there is minimal
Tilmann Singer skrev:
But the subselect is not fast for the user with many relationships and
matched rows at the beginning of the sorted large_table:
testdb=# EXPLAIN ANALYZE SELECT * FROM large_table lt
WHERE user_id IN (SELECT contact_id FROM relationships WHERE
user_id=5)
ORDER
* Nis Jørgensen [EMAIL PROTECTED] [20070730 18:33]:
It seems to me the subselect plan would benefit quite a bit from not
returning all rows, but only the 10 latest for each user. I believe the
problem is similar to what is discussed for UNIONs here:
http://groups.google.dk/group
With CLOG 16 the drp[s comes at about 1150 users with the following lock
stats
bash-3.00# ./4_lwlock_waits.d 16404
Lock IdMode Count
ProcArrayLock Shared 2
XidGenLock Exclusive 2
XidGenLock
Tilmann Singer skrev:
* Nis Jørgensen [EMAIL PROTECTED] [20070730 18:33]:
It seems to me the subselect plan would benefit quite a bit from not
returning all rows, but only the 10 latest for each user. I believe the
problem is similar to what is discussed for UNIONs here:
http
Jay Kang wrote:
Hello,
I'm currently trying to decide on a database design for tags in my web
2.0 application. The problem I'm facing is that I have 3 separate tables
i.e. cars, planes, and schools. All three tables need to interact with
the tags, so there will only be one universal set of
valgog wrote:
On Jul 25, 2:14 am, Lew [EMAIL PROTECTED] wrote:
How about two indexes, one on each column? Then the indexes will cooperate
when combined in a WHERE clause.
http://www.postgresql.org/docs/8.2/interactive/indexes-bitmap-scans.html
I don't believe the index makes a semantic
Luke,
ZFS tuning is not coming from general suggestion ideas, but from real
practice...
So,
- limit ARC is the MUST for the moment to keep your database running
comfortable (specially DWH!)
- 8K blocksize is chosen to read exactly one page when PG ask to
read one page - don't mix it with
Hi Dimitri,
Can you post some experimental evidence that these settings matter?
At this point we have several hundred terabytes of PG databases running on ZFS,
all of them setting speed records for data warehouses.
We did testing on these settings last year on S10U2, perhaps things have
In a followup to a question I put forward here on performance which I
traced to the stats bug (and fixed it). Now I'm trying to optimize
that query and... I'm getting confused fast...
I have the following (fairly complex) statement which is run with some
frequency:
select post.forum,
Merlin Moncure [EMAIL PROTECTED] writes:
third (but unlikely) possibility is there are various dropped tables,
etc which need to be deleted but there are stale postgresql processes
holding on to the fd. This would only happen following a postmaster
crash or some other bizarre scenario, but
On Jul 30, 2007, at 9:04 AM, Steven Flatt wrote:
On 7/28/07, Jim C. Nasby [EMAIL PROTECTED] wrote: What are your
vacuum_cost_* settings? If you set those too aggressively
you'll be in big trouble.
autovacuum_vacuum_cost_delay = 100
Wow, that's *really* high. I don't think I've ever set it
20 matches
Mail list logo
