Re: [PERFORM] Hardware recommendations to scale to silly load

2003-09-02 Thread Greg Stark
Vivek Khera [EMAIL PROTECTED] writes: The front-end small processes get to deal with your dialup customers trickling down the data since it buffers your backend for you. Huh. Well, I used to think this. But I think I was wrong. I used to have apache proxy servers running in front of the

Re: [PERFORM] slow plan for min/max

2003-09-08 Thread Greg Stark
scott.marlowe [EMAIL PROTECTED] writes: Basically, Postgresql uses an MVCC locking system that makes massively As discussed, uh, a few days ago, this particular problem is not caused by MVCC but by postgres having a general purpose aggregate system and not having special code for handling

Re: [PERFORM] inferior SCSI performance

2003-10-02 Thread Greg Stark
Unfortunately, while there are companies hawking SSDs, they are in the you'll have to talk to our salescritter for pricing category, which means that they must be ferociously expensive. :-(. the cheapest I found was the one with external backup power was ~1.8k$ for 2GB PCI device

Re: [PERFORM] count(*) slow on large tables

2003-10-02 Thread Greg Stark
Christopher Browne [EMAIL PROTECTED] writes: It would be very hairy to implement it correctly, and all this would cover is the single case of SELECT COUNT(*) FROM SOME_TABLE; If you had a single WHERE clause attached, you would have to revert to walking through the tuples looking for the

Re: [PERFORM] Speeding up Aggregates

2003-10-08 Thread Greg Stark
Rod Taylor [EMAIL PROTECTED] writes: On Fri, 2003-10-03 at 17:53, Dror Matalon wrote: On Fri, Oct 03, 2003 at 05:44:49PM -0400, Rod Taylor wrote: It is too bad the (channel, link) index doesn't have dtstamp at the end of it, otherwise the below query would be a gain (might be a small

Re: [PERFORM] Speeding up Aggregates

2003-10-09 Thread Greg Stark
Dror Matalon [EMAIL PROTECTED] writes: Actually what finally sovled the problem is repeating the dtstamp last_viewed in the sub select That will at least convince the optimizer to use an index range lookup. But it still will have to scan every record that matches channel==$1, link==$2, and

Re: [PERFORM] backup/restore - another area.

2003-10-09 Thread Greg Stark
Jeff [EMAIL PROTECTED] writes: Idea #1: Use an LVM and take a snapshop - archive that. From the way I see it. the downside is the LVM will use a lot of space until the snapshot is removed. Also PG may be in a slightly inconsistant state - but this should appear to PG the same as if the

Re: [PERFORM] Speeding up Aggregates

2003-10-09 Thread Greg Stark
Dror Matalon [EMAIL PROTECTED] writes: Ouch. I just double checked and you're right. Is this considered a bug, or just an implementation issue? Call it a wishlist bug. The problem is it would be a hard feature to implement properly. And none of the people paid to work on postgres by various

Re: [PERFORM] slow select

2003-10-24 Thread Greg Stark
Medora Schauer [EMAIL PROTECTED] writes: Merge Join (cost=0.00..287726.10 rows=100221 width=58) (actual time=61.60..5975.63 rows=100425 loops=1) Merge Cond: ((outer.shot_line_num = inner.shot_line_num) AND (outer.shotpoint = inner.shotpoint)) - Index Scan using hsot_record_idx on

Re: [PERFORM] vacuum locking

2003-10-24 Thread Greg Stark
Rob Nagler [EMAIL PROTECTED] writes: Incorrect. If the tuples smaller, Oracle does the right thing. If there's enough space in the page, it shifts the tuples to make room. That's what pctfree, pctused and pctincrease allow you to control. It's all in memory so its fast, and I don't think it

Re: [PERFORM] vacuum locking

2003-10-24 Thread Greg Stark
Rob Nagler [EMAIL PROTECTED] writes: Mario Weilguni writes: of course both approaches have advantages, it simply depends on the usage pattern. A case where oracle really rules over postgresql are m--n connection tables where each record consist of two foreign keys, the overwrite

Re: [PERFORM] Performance Concern

2003-10-25 Thread Greg Stark
John Pagakis [EMAIL PROTECTED] writes: UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM baz WHERE customer_id IS NULL LIMIT 1000 ); Do an explain analyze on this query. I bet it's doing two sequential scans. Unfortunately in 7.3 the WHERE IN type of clause is poorly

Re: [PERFORM] Various performance questions

2003-10-26 Thread Greg Stark
Dror Matalon [EMAIL PROTECTED] writes: explain analyze select count(*) from items where channel 5000; QUERY PLAN --

Re: [PERFORM] Various performance questions

2003-10-27 Thread Greg Stark
Christopher Browne [EMAIL PROTECTED] writes: In the last exciting episode, [EMAIL PROTECTED] (Dror Matalon) wrote: I was answering an earlier response that suggested that maybe the actual counting took time so it would take quite a bit longer when there are more rows to count. That was my

Re: [PERFORM] vacuum locking

2003-10-27 Thread Greg Stark
Rob Nagler [EMAIL PROTECTED] writes: I didn't find ALTER SESSION for postgres (isn't that Oracle?), so I set sort_mem in the conf file to 512000, restarted postrgres. Reran the simpler query (no name) 3 times, and it was still 27 secs. Sorry, I don't know how that bubbled up from the depths

Re: [PERFORM] Various performance questions

2003-10-27 Thread Greg Stark
Neil Conway [EMAIL PROTECTED] writes: On Sun, 2003-10-26 at 22:49, Greg Stark wrote: What version of postgres is this?. In 7.4 (and maybe 7.3?) count() uses an int8 to store its count so it's not limited to 4 billion records. Unfortunately int8 is somewhat inefficient as it has

Re: [PERFORM] Very Poor Insert Performance

2003-10-27 Thread Greg Stark
Damien Dougan [EMAIL PROTECTED] writes: Our batch upload is performing a number of stored procedures to insert data on the database. Initially, this results in quite good performance, but rapidly spirals down to approximately 1 per second after some minutes. It's fairly unlikely anyone

Re: [PERFORM] Various performance questions

2003-10-27 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Greg is correct. int8 is a pass-by-reference datatype Just to keep the conversation on track. the evidence from this particular post seems to indicate that my theory was wrong and the overhead for count(*) is _not_ a big time sink. It seems to be at most 10%

Re: [PERFORM] Various performance questions

2003-10-27 Thread Greg Stark
In fact the number of records seems to be almost irrelevant. A sequential scan takes almost exactly the same amount of time up until a critical region (for me around 10 records) at which point it starts going up very quickly. It's almost as if it's doing some disk i/o, but I'm watching

Re: [PERFORM] vacuum locking

2003-10-29 Thread Greg Stark
Rob Nagler [EMAIL PROTECTED] writes: One of the reason postgres is faster on the q1-4 is that postgres supports OFFSET/LIMIT, and oracle doesn't. q7 and q8 are the queries that I've referred to recently (avg of group by). Well the way to do offset/limit in Oracle is: SELECT * FROM (

Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)

2003-10-29 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: (I'd argue that the SQL generator is broken anyway ;-) if it generates such horrible conditions as that. Or maybe the real problem is that the database schema is a mess and needs rethinking.) I had the same reaction when I first saw those queries. But I

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

2003-10-30 Thread Greg Stark
Rajesh Kumar Mallah [EMAIL PROTECTED] writes: - Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=164) Filter: ((effectiveid = id) AND ((type)::text = 'ticket'::text) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text))) This query has to read through every

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

2003-10-30 Thread Greg Stark
Rajesh Kumar Mallah [EMAIL PROTECTED] writes: rt3=# explain SELECT DISTINCT main.* FROM ((( (Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) ) JOIN

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

2003-10-30 Thread Greg Stark
Rajesh Kumar Mallah [EMAIL PROTECTED] writes: Nopes the query are not Equiv , earlier one returns 4 rows and the below one none, Sorry, i lowercased a string constant and dropped the lower() on email. Try this: SELECT * FROM tickets WHERE id IN ( SELECT groups.instance

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

2003-10-30 Thread Greg Stark
Well, you might want to try the EXISTS version. I'm not sure if it'll be faster or slower though. In theory it should be the same. Hum, I didn't realize the principals table was the largest table. But Postgres knew that so one would expect it to have found a better plan. The IN/EXISTS handling

Re: [PERFORM] Pg+Linux swap use

2003-10-31 Thread Greg Stark
Bill Moran [EMAIL PROTECTED] writes: Just for an additional viewpoint. I'm finishing up a project based on FreeBSD and PostgreSQL. The target server is a Dual 2.4G Intel machine. I have tested the application with hyperthreading enabled and disabled. To all appearances, enabling

Re: [PERFORM] Pg+Linux swap use

2003-11-02 Thread Greg Stark
William Yu [EMAIL PROTECTED] writes: Rob Sell wrote: Not being one to hijack threads, but I haven't heard of this performance hit when using HT, I have what should all rights be a pretty fast server, dual 2.4 Xeons with HT 205gb raid 5 array, 1 gig of memory. And it is only 50% as fast

Re: [PERFORM] *very* slow query to summarize data for a month ...

2003-11-11 Thread Greg Stark
Dennis Bjorklund [EMAIL PROTECTED] writes: On Mon, 10 Nov 2003, Marc G. Fournier wrote: explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic FROM company c, traffic_logs ts WHERE c.company_id = ts.company_id AND month_trunc(ts.runtime) =

Re: [PERFORM] *very* slow query to summarize data for a month ...

2003-11-11 Thread Greg Stark
Marc G. Fournier [EMAIL PROTECTED] writes: On Tue, 11 Nov 2003, Greg Stark wrote: Actually you might be able to get the same effect using function indexes like: create index i on traffic_log (month_trunc(runtime), company_id) had actually thought of that one ... is it something

Re: [PERFORM] *very* slow query to summarize data for a month ...

2003-11-11 Thread Greg Stark
Marc G. Fournier [EMAIL PROTECTED] writes: Just as a side note, just doing a straight scan for the records, with no SUM()/GROUP BY involved, with the month_trunc() index is still 8k msec: Well so the problem isn't the query at all, you just have too much data to massage online. You can

Re: [PERFORM] Maximum Possible Insert Performance?

2003-11-24 Thread Greg Stark
William Yu [EMAIL PROTECTED] writes: You're right, though, mirroring a solid state drive is pretty pointless; if power fails, both mirrors are dead. Actually no. Solid state memory is non-volatile. They retain data even without power. Note that flash ram only has a finite number of write

Re: [PERFORM] Impossibly slow DELETEs

2003-11-26 Thread Greg Stark
Is it possible another connection has updated the record and not committed, and it takes a minute for the connection to time out and commit or roll back? -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL

Re: [PERFORM] Update performance ... is 200,000 updates per hour what I should expect?

2003-12-02 Thread Greg Stark
Erik Norvelle [EMAIL PROTECTED] writes: Here's the query I am running: update indethom set query_counter = nextval('s2.query_counter_seq'), -- Just for keeping track of how fast the query is running sectref = (select clavis from s2.sectiones where

Re: [PERFORM] A question on the query planner

2003-12-02 Thread Greg Stark
Jared Carr [EMAIL PROTECTED] writes: Furthermore noticed that in the following query plan it is doing the sequential scan on quotes first, and then doing the sequential on zips. IMHO this should be the other way around, since the result set for zips is considerably smaller especially give

Re: [PERFORM] A question on the query planner

2003-12-02 Thread Greg Stark
Jared Carr [EMAIL PROTECTED] writes: Greg Stark wrote: Merge Cond: (outer.?column7? = inner.?column5?) Well it looks like you have something strange going on. What data type is car_id in each table? car_id is a varchar(10) in both tables. Well for some reason it's being

Re: [PERFORM] A question on the query planner

2003-12-02 Thread Greg Stark
Jared Carr [EMAIL PROTECTED] writes: Greg Stark wrote: Well it looks like you have something strange going on. What data type is car_id in each table? car_id is a varchar(10) in both tables. Huh. The following shows something strange. It seems joining on two varchars no longer works

Re: [PERFORM] A question on the query planner

2003-12-02 Thread Greg Stark
Greg Stark [EMAIL PROTECTED] writes: Huh. The following shows something strange. Worse, with enable_hashjoin off it's even more obvious something's broken: test=# set enable_hashjoin = off; SET test=# explain select * from a,b where a.x=b.x; QUERY PLAN

Re: [PERFORM] Minimum hardware requirements for Postgresql db

2003-12-03 Thread Greg Stark
scott.marlowe [EMAIL PROTECTED] writes: 3) Estimated number of transactions to be written into the Postgresql db is around 15000 records per day. The growth rate in terms of number of connections is around 10% per year and the data retention is kept on average at least for 18 months

Re: [PERFORM] Tuning Techniques To Avoid?

2004-01-02 Thread Greg Stark
Christopher Browne [EMAIL PROTECTED] writes: Here's a scheme for query optimization that probably needs to be avoided in that it would run afoul of a patent held by Oracle... What does this have to do with Hervé Piedvache's post Why memory is not used? ? -- greg

Re: [PERFORM] Inefficient SELECT with OFFSET and LIMIT

2004-01-06 Thread Greg Stark
Clive Page [EMAIL PROTECTED] writes: SELECT myfunc(mycol) FROM table LIMIT 50 OFFSET 1 ; It looks as if OFFSET is implemented just be throwing away the results, until the OFFSET has been reached. It would be nice if OFFSET could be implemented in some more efficient way. You could

[PERFORM] RAID array stripe sizes

2004-01-07 Thread Greg Stark
Does anyone have any data to support arguing for a particular stripe size in RAID-0? Do large stripe sizes allow drives to stream data more efficiently or defeat read-ahead? -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your

Re: [PERFORM] Explain not accurate

2004-01-11 Thread Greg Stark
Richard van den Berg [EMAIL PROTECTED] writes: Hi there, I am quite new to postgresql, and love the explain feature. It enables us to predict which SQL queries needs to be optimized before we see any problems. However, I've run into an issue where explain tells us a the costs of a quiry

Re: [PERFORM] database performance and query performance question

2004-01-22 Thread Greg Stark
Shea,Dan [CIS] [EMAIL PROTECTED] writes: Indexes: forecastelement_vrwi_idx btree (valid_time,region_id.wx_element.issue_time) explain analyze SELECT DISTINCT ON (valid_time) to_char(valid_time,'MMDDHH24MISS') AS valid_time, value from ( SELECT

Re: [PERFORM] High Performance/High Reliability File system on SuSE64

2004-01-26 Thread Greg Stark
Christopher Browne [EMAIL PROTECTED] writes: Ah, but there is a bit of a 'problem' nonetheless; XFS is not 'officially supported' as part of the Linux kernel until version 2.6, which is still pretty bleeding edge. Until 2.6 solidifies a bit more (aside: based on experiences with 2.6.0,

Re: [PERFORM] query optimization question

2004-01-30 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Jack Coates [EMAIL PROTECTED] writes: yup -- here it is. It will probably be a nasty mess after linewrap gets done with it, yup, sure is :-( If I was familiar with the layout I could probably decipher where the line breaks are supposed to be, but

Re: [PERFORM] severe performance issue with planner

2004-03-12 Thread Greg Stark
The other posts about using explicit joins and using stored procedures are both good points. But I have a few other comments to make: Eric Brown [EMAIL PROTECTED] writes: WHERE w0.wid 0 AND w0.pinyin = 'zheng4' AND w0.def_exists = 't' AND w0.sequence = 0 AND w1.wid =

Re: [PERFORM] severe performance issue with planner

2004-03-12 Thread Greg Stark
Sorry, I forgot a key clause there: Greg Stark [EMAIL PROTECTED] writes: select w8.wid, w8.variant, w8.num_variants, sum_text(w8.unicode) as unicodes, sum_text(w8.pinyin) as pinyins from ( select wid,variant, from words where

Re: [PERFORM] Large CASE-statement is pretty slow?

2004-03-15 Thread Greg Stark
Arjen van der Meijden [EMAIL PROTECTED] writes: Of course I wanted to know how long it'd take on postgresql, selecting the pkey-field only (without the case) took also some 0.7 seconds (the entire table may have been more). But the CASE-version took 9026139.201 ms, i.e. over 9000 seconds

Re: [PERFORM] Large CASE-statement is pretty slow?

2004-03-15 Thread Greg Stark
Arjen van der Meijden [EMAIL PROTECTED] writes: Well, I have discarded this type of query as too inefficient and found a better way Loading the mapping into a table with an index and doing an update using from to do a join seems likely to end up being the most efficient method. Postgres would

Re: [PERFORM] SLOW query with aggregates

2004-03-23 Thread Greg Stark
A Palmblad [EMAIL PROTECTED] writes: GroupAggregate (cost=0.00..338300.34 rows=884 width=345) (actual time=86943.272..382718.104 rows=3117 loops=1) - Merge Join (cost=0.00..93642.52 rows=1135610 width=345) (actual time=0.148..24006.748 rows=1120974 loops=1) I think the reason you're

Re: [PERFORM] Index Performance Help

2004-04-01 Thread Greg Stark
Damien Dougan [EMAIL PROTECTED] writes: Sample analyze output for an initial query: hydradb=# explain analyze select * from pvsubscriber where actorid = 'b3432-asdas-232-Subscriber793500'; I take it pvsubscriber is a view? What's the definition of your view? - Index Scan using

Re: [PERFORM] single index on more than two coulumns a bad thing?

2004-04-01 Thread Greg Stark
Palle Girgensohn [EMAIL PROTECTED] writes: Is it always bad to create index xx on yy (field1, field2, field3); All generalisations are false... Seriously, it's true that as the length of your index key gets longer the harder and harder it is to justify it. That doesn't mean they're always

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Greg Stark
Bruno Wolff III [EMAIL PROTECTED] writes: I have seen exactly this happen a number of times over the last several years. However there is still only one Tom Lane implementing the improvements. Ob: Well clearly the problem is we need more Tom Lanes. -- greg ---(end

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Greg Stark
Joshua D. Drake [EMAIL PROTECTED] writes: while($howmany--) { push @morepgGurus, $pgGuru; } This is just wrong... yeah, it would have been much clearer written as: push @morepgGurus, ($pgGuru)x$howmany; Or at least the perlish: for (1..$howmany) instead of C style while syntax. Ok. I

Re: [PERFORM] Poor performance of group by query

2004-04-16 Thread Greg Stark
stats=# explain analyze SELECT work_units, min(raw_rank) AS rank FROM Trank_work_overall GROUP BY work_units; ... raw_rank | bigint | work_units | bigint | If you create a copy of the same table using regular integers does that run fast? And a copy of the table using bigints is

Re: [PERFORM] Horribly slow hash join

2004-04-17 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: We could change the hash function, perhaps, but then we'd just have different cases where there's a problem ... hashing will always fail on *some* set of inputs. Sure, but completely ignoring part of the input seems like an unfortunate choice of hash

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: (Also, I have been harboring some notions of supporting cross-type hash joins for integer types, which will not work unless small int8 values hash the same as int4 etc

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-18 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: So in the short term I think we have to tell people that Xeon MP is not the most desirable SMP platform to run Postgres on. (Josh thinks that the specific motherboard chipset being used in these machines might share some of the blame too. I don't have any

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Greg Stark
Dennis Bjorklund [EMAIL PROTECTED] writes: On Sun, 18 Apr 2004, Bruno Wolff III wrote: Another option would be to put the numbers into two int4s. For int4 or smaller types one of these would be zero. int8s would be split between the two. The hash function would then be defined on the

Re: [PERFORM] Horribly slow hash join

2004-04-19 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: If the hash tables were made a power of two then it would be possible to mix the bits of the 32 bit value and just mask off the unneeded bits. I've found one page via google that mentions mixing bits in a hash function

Re: [PERFORM] Horribly slow hash join

2004-04-19 Thread Greg Stark
Dave Cramer [EMAIL PROTECTED] writes: Here's an interesting link that suggests that hyperthreading would be much worse. Uh, this is the wrong thread. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL

Re: [PERFORM] Horribly slow hash join

2004-05-04 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Modding by a *non* power of 2 (esp. a prime) mixes the bits quite well, and is likely faster than any multiple-instruction way to do the same. The quoted article seems to be by someone who has spent a lot of time counting assembly cycles and none at all

Re: [PERFORM] linux distro for better pg performance

2004-05-04 Thread Greg Stark
Joseph Shraibman [EMAIL PROTECTED] writes: J. Andrew Rogers wrote: Do these features make a difference? Far more than you would imagine. On one postgres server I just upgraded, we went from a 3Ware 8x7200-RPM RAID-10 configuration to an LSI 320-2 SCSI 3x10k RAID-5, with 256M Is raid

Re: [PERFORM] PostgreSQL on VMWare vs Windows vs CoLinux

2004-06-02 Thread Greg Stark
Vitaly Belman [EMAIL PROTECTED] writes: With all that said, VMWare is badly suited for running a database, while CoLinux can be run as a service (didn't try it yet though), VMWare always sits there, it is slow to go up, slow to go down and generally feels like a system hog. Uhm, it

Re: [PERFORM] PostgreSQL on VMWare vs Windows vs CoLinux

2004-06-02 Thread Greg Stark
Matthew Nuzum [EMAIL PROTECTED] writes: I have colinux running on a Fedora Core 1 image. I have the rhdb 3 (or PostgreSQL RedHat Edition 3) on it running. Here are tests with fsync on and off: FSYNC OFF FSYNC ON RUN 136.9 142.0 124.5149.1 1 122.1 126.7

Re: [PERFORM] Most transactions per second on largest box?

2004-06-04 Thread Greg Stark
[EMAIL PROTECTED] writes: Currently we are running a dual cpu dell blade server on redhat linux (2.4?) and PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96, raid5 and am using sqlrelay for connection pooling. It works fine under ordinary load but bogs down too much under these

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Greg Stark
Michael Nonemacher [EMAIL PROTECTED] writes: Agreed. We originally created the indexes this way because we sometimes do searches where one of the columns is constrained using =, and the other using a range search, but it's not clear to me how much Postgres understands multi-column indexes.

Re: [PERFORM] Performance over a LAN

2004-07-23 Thread Greg Stark
William Carney [EMAIL PROTECTED] writes: The machines used are P4s running FreeBSD 5.2.1. The Postgres version is 7.4.3. Can anyone tell me why there's such a big difference? You're going to have to run tcpdump and see where the delays are. It might be hard to decode the postgres protocol

Re: [PERFORM] arrays and indexes

2004-07-26 Thread Greg Stark
Ross J. Reedstrom [EMAIL PROTECTED] writes: In the new schema, the same thing is: SELECT * from content where 42 = ANY (authors); Works fine, but for the life of me I can't find nor figure out how to build an index that will be used to speed this along. Any ideas? Well that's basically

Re: [PERFORM] arrays and indexes

2004-07-26 Thread Greg Stark
Ross J. Reedstrom [EMAIL PROTECTED] writes: These groups _really are_ ideal for Joe Conway's work on arrays: we need ordered vectors, so we'd be sorting all the time, otherwise. They're static, and they're read only. The one thing they're not is fixed, known size (Sorry Merlin). They work

Re: [PERFORM] arrays and indexes

2004-07-26 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: I still don't really know why it failed, but after two days building the index I gave up. Sounds like a bug to me. Could you put together a test case? At the time I contacted one of the GiST authors and we went over things for a while. They diagnosed

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Greg Stark
Interestingly, it is possible to rewrite the above query by switching and with or and = with . However when written that way, the planner almost never gets it right. Well, note it's still not really getting it right even in your case. It's doing an index scan on a=a1 but if you have lots of

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Greg Stark
Merlin Moncure [EMAIL PROTECTED] writes: do it for multi-column keys. It seems it would be nice if some syntax similar to (a,b,c) (a1,b1,c1) worked for this. 'nice' would be an understatement... if the above syntax is not defined in the standard, I would humbly suggest, well, beg for

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-28 Thread Greg Stark
Stephan Szabo [EMAIL PROTECTED] writes: Given the comment on make_row_op, /* * XXX it's really wrong to generate a simple AND combination for = * =. We probably need to invent a new runtime node type to handle * those correctly. For the moment, though, keep on doing this ...

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-28 Thread Greg Stark
Greg Stark [EMAIL PROTECTED] writes: Fixing it to write out complex boolean expressions wouldn't be too hard, but I'm not clear it would be worth it, since I suspect the end result would be as the comment indicates, to introduce a new runtime node. Just to prove that it isn't really all

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-28 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: The only reason the code in parse_expr.c appears new is that the functionality used to be in gram.y. Ah, that was what I was missing. Though it's odd since it seems there was code in parse_expr.c to handle the = case specially. I'd like to see this fixed

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-28 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: One thing I did not like about your first pass is that it makes unsupportable assumptions about there being a semantic relationship between operators named, say, '' and '='. Hm, I think I even had caught that issue on the mailing list previously. In that

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-29 Thread Greg Stark
Merlin Moncure [EMAIL PROTECTED] writes: Well, if and when the rowtype comparison can be made to work over multi part keys (and the optimizer is made to do tricks there), postgres can be made to give much better generalized ISAM access. In the meantime, I'll just troubleshoot specific cases

[PERFORM] SSD Drives

2004-08-01 Thread Greg Stark
To the person who was looking for a $5k midlevel SSD drive (sorry, I hit 'd' too fast): http://www.tigicorp.com/tigijet_exp_s.htm I found this via this interesting survey of SSD products: http://www.storagesearch.com/ssd-buyers-guide.html Incidentally it seems the popular Platypus SSD PCI

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

2004-08-26 Thread Greg Stark
Bruce Momjian [EMAIL PROTECTED] writes: Updated TODO item: o Automatically maintain clustering on a table This would require some background daemon to maintain clustering during periods of low usage. It might also require tables to be only paritally

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

2004-08-27 Thread Greg Stark
Bruce Momjian [EMAIL PROTECTED] writes: Agreed. What I am wondering is with our system where every update gets a new row, how would this help us? I know we try to keep an update on the same row as the original, but is there any significant performance benefit to doing that which would

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

2004-08-27 Thread Greg Stark
Bruce Momjian [EMAIL PROTECTED] writes: but is there any significant performance benefit to doing that which would offset the compaction advantage? Just as a side comment. Setting PCTFREE 0 PCTUSED 100 on tables that have no updates on them has an astonishingly big effect on speed. So the

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

2004-08-28 Thread Greg Stark
Bruce Momjian [EMAIL PROTECTED] writes: Don't you think this will permit also to avoid extra disk seek and cache invalidation? If you are updating the row (0,1) I think is less expensive put the new version in (0,2) instead of thousand line far from that point. Well if the other buffer a

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-29 Thread Greg Stark
Mr Pink [EMAIL PROTECTED] writes: AFAIK postgres doesn't peek at values used in a query when optimizing Of course it does. However sometimes things don't work perfectly. To get good answers rather than just guesses we'll need two things: . What version of postgres are you using. . The

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-30 Thread Greg Stark
[I'm actually responding to the previous post from Tom Lane, but I've deleted it and the archives seem to be down again.] The assumption being made is that the first provided result is representative of all future results. I don't see any reason that making this assumption of all stable

Re: [PERFORM] Multiple Uniques

2004-09-02 Thread Greg Stark
Markus Schaber [EMAIL PROTECTED] writes: logigis=# explain select count(id) from (select ref_in_id as id from streets union select nref_in_id as id from streets) as blubb; QUERY PLAN

Re: [PERFORM] Multiple Uniques

2004-09-10 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Neil Conway [EMAIL PROTECTED] writes: How many cycles are we really talking about, though? I have a patch which I'll send along in a few days which implements a similar optimization: if a subselect is referenced by EXISTS or IN, we can discard

Re: [PERFORM] Partitioning

2004-09-15 Thread Greg Stark
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 it currently possible to query which subtable a record came from though?

Re: [PERFORM] Comparing user attributes with bitwise operators

2004-09-16 Thread Greg Stark
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

Re: [PERFORM] Comparing user attributes with bitwise operators

2004-09-16 Thread Greg Stark
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

Re: [PERFORM] Comparing user attributes with bitwise operators

2004-09-18 Thread Greg Stark
Patrick Clery [EMAIL PROTECTED] writes: PLAN - Limit

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Greg Stark
Gaetano Mendola [EMAIL PROTECTED] writes: hash_join = on - Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (actual time=0.017..0.038 rows=16 loops=1) hash_join = off - Seq Scan on lookup_tipo_evento le (cost=0.00..1.16

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Greg Stark
Dennis Bjorklund [EMAIL PROTECTED] writes: On 22 Sep 2004, Greg Stark wrote: Actually this looks like it's arguably a bug to me. Why does the hash join execute the sequential scan at all? Shouldn't it also like the merge join recognize that the other hashed relation is empty and skip

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Yeah, I was just looking at doing that. Well I imagine it takes you as long to read my patch as it would for you to write it. But anyways it's still useful to me as exercises. It would also be interesting to prefetch one row from the outer table and fall

Re: [PERFORM] NAS, SAN or any alternate solution ?

2004-09-22 Thread Greg Stark
Andrew Hammond [EMAIL PROTECTED] writes: My goal is to tune the disk / filesystem on our prototype system. It's an EMC disk array, so sectors on disk are 512 bytes of usable space. We've decided to go with RAID 10 since the goal is to maximize performance. Currently the raid element size is

Re: [PERFORM] Caching of Queries

2004-09-23 Thread Greg Stark
Jason Coene [EMAIL PROTECTED] writes: All of our postgres processes end up in the semwai state - seemingly waiting on other queries to complete. If the system isn't taxed in CPU or disk, I have a good feeling that this may be the cause. Well, it's possible contention of some sort is an

Re: [PERFORM] why my query is not using index??

2004-10-11 Thread Greg Stark
John Meinel [EMAIL PROTECTED] writes: As Janning mentioned, sometimes sequential scans *are* faster. If the number of entries that will be found is large compared to the number of total entries (I don't know the percentages, but probably 30-40%), Actually 30%-40% is unrealistic. The

Re: [PERFORM] First set of OSDL Shared Mem scalability results, some

2004-10-13 Thread Greg Stark
Jan Wieck [EMAIL PROTECTED] writes: On 10/8/2004 10:10 PM, Christopher Browne wrote: [EMAIL PROTECTED] (Josh Berkus) wrote: I've been trying to peg the sweet spot for shared memory using OSDL's equipment. With Jan's new ARC patch, I was expecting that the desired amount of

Re: [PERFORM] Query planner problem

2004-10-03 Thread Greg Stark
Russell Smith [EMAIL PROTECTED] writes: The Index does not store NULL values This is false. Though the fact that NULL values are indexed in postgres doesn't help with this poster's actual problem. -- greg ---(end of broadcast)--- TIP 2: you

Re: [PERFORM] Excessive context switching on SMP Xeons

2004-10-06 Thread Greg Stark
Alan Stange [EMAIL PROTECTED] writes: A few quick random observations on the Xeon v. Opteron comparison: - running a dual Xeon with hyperthreading turned on really isn't the same as having a quad cpu system. I haven't seen postgresql specific benchmarks, but the general case has been that

  1   2   3   4   >