Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-10 Thread PFC
I'm curious, why do you think that's serious ? What do you really expect Simply because I don't like VB non .NET, but C# is a much much better language, and even VB.NET is decent. to do in the stored procedure ? Anything of consequence will seriously degrade performance if you select it in

[PERFORM] MOVE command

2005-01-13 Thread PFC
Hello, Here I'm implementing a session management, which has a connections table partitioned between active and archived connections. A connection represents a connection between a user and a chatroom. I use partitioning for performance reasons. The active table contains all the

Re: [PERFORM] Index on a function and SELECT DISTINCT

2005-01-17 Thread PFC
Try : EXPLAIN SELECT get_year_trunc(pub_date) as foo FROM ... GROUP BY foo Apart from that, you could use a materialized view... db=# EXPLAIN SELECT DISTINCT get_year_trunc(pub_date) FROM Ah, that makes sense. So is there a way to optimize SELECT DISTINCT queries that have no

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-26 Thread PFC
Supposing your searches display results which are rows coming from one specific table, you could create a cache table : search_id serial primary key index_n position of this result in the global result set result_id id of the resulting row. Then, making a search with 50k results

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-26 Thread PFC
http://borg.postgresql.org/docs/8.0/interactive/storage-page-layout.html If you vacuum as part of the transaction it's going to be more efficient of resources, because you have more of what you need right there (ie: odds are that you're on the same page as the old tuple). In cases like that it

Re: [PERFORM] poor performance of db?

2005-01-26 Thread PFC
Every time I tested an idea to speed it up, I got exactly the same loading time on a Athlon 1800+, 256Mb RAM, 20Gb PATA computer as compared to a Dual Opteron 246, 1Gb RAM, 70Gb WD Raptor SATA server. Now, why a dual opteron machine can't perform any faster than a lowly 1800+ athlon in

Re: [PERFORM] 200 times slower then MSSQL??

2005-01-26 Thread PFC
with about 8000 rows. For this table query: SELECT MAX(MsgSeqNum),MAX(LogTimestamp) FROM ELT_tcli_MessageLog WHERE LogTimestamp = '0' AND IsFromCounterParty = 'Y' AND IsOutOfSequence = 'N' AND ConnectionName = 'DB_BENCHMARK' AND LogTimestamp IN (SELECT MAX(LogTimestamp)

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-26 Thread PFC
The problem with this approach is TTFB (Time to first Byte). The initial query is very slow, but additional requests are fast. In most situations we do not want the user to have to wait a disproportionate amount of time for the initial query. If this is the first time using the system this will

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-27 Thread PFC
However, it seems that integer integer[] does not exist : Try intset(id) int[]. intset is an undocumented function :) I'm going to add intset() to README. SELECT * FROM table WHERE id int[] Mm. intset(x) seems to be like array[x] ? Actually what I want is the opposite. I have a btree

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-27 Thread PFC
What you want is called UNNEST. It didn't get done in time for 8.0. But if what you have is an array of integers the int_array_enum() function I quoted in the other post is basically that. Yes, I used it, thanks. That's what I wanted. The query plans are good. You don't really need the

Re: [PERFORM] Performance problem with semi-large tables

2005-01-29 Thread PFC
So are you suggesting as a general rule then that sub-queries are the way to force a specific join order in postgres? If that is the case, I will do this from now on. I'll try to explain a bit better... Here's your original query : select s.*, ss.* from shipment s,

Re: [PERFORM] Performance problem with semi-large tables

2005-01-30 Thread PFC
SELECT * FROM shipment_status WHERE release_code_id = constant ORDER BY release_code_id DESC, date DESC LIMIT 100; I have done this in other queries where sorting by both release code and date were important. You are right, it is very fast and I do have this index in play. However, most of

Re: [PERFORM] GiST indexes and concurrency (tsearch2)

2005-02-03 Thread PFC
Do you have anything performing any updates or inserts to this table, even if it does not update the gist column, even if it does not update anything ? ---(end of broadcast)--- TIP 6: Have you searched our list archives?

Re: [PERFORM] Performance Tuning

2005-02-09 Thread PFC
As a side note, I learned something very interesting for our developers here. We had been doing a drop database and then a reload off a db dump from our live server for test data. This takes 8-15 minutes depending on the server (the one above takes about 8 minutes). I learned through

Re: [PERFORM] Benchmark (slightly off topic but oh well)

2005-02-11 Thread PFC
For example, I am a developer of Mambo, a PHP-based CMS application, and am porting the mysql functions to ADOdb so I can use grown-up databases ;-) Just yesterday I optimized a query for a website running MySQL. It's the 'new products' type query : SELECT product_id, pd.product_name,

Re: [PERFORM] Benchmark

2005-02-11 Thread PFC
In terms of performance, Oracle is to Postgres as Postgres is to Mysql: More complexity, more overhead, more layers of abstraction, but in the long run it pays off when you need it. (Only without the user-friendliness of either open-source softwares.) I don't find postgres complex... I find

Re: [PERFORM] String matching

2005-02-14 Thread PFC
normally you shouldn't have to do anything, it should just work : select field from table where field like 'abc%' CREATE INDEX ... ON table( field ); that's all If it does not use the index, I saw on the mailing list that the locale could be an issue.

Re: [PERFORM] seq scan cache vs. index cache smackdown

2005-02-28 Thread PFC
(For those not knowing - it's ReadFile/WriteFile where you pass an array of this many bytes to this address as parameters) Isn't that like the BSD writev()/readv() that Linux supports also? Is that something we should be using on Unix if it is supported by the OS? Nope, readv()/writev()

Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with

2005-03-07 Thread PFC
From the Linux Kernel (make menuconfig) there seem to be two new reliable sources for timing information. Note the remark about Time Stamp Counter below. Question is, which one of these (or others) are your API functions using ? I have absolutely no idea ! CONFIG_HPET_TIMER:

Re: [PERFORM] Questions about 2 databases.

2005-03-11 Thread PFC
My web app does lots of inserts that aren't read until a session is complete. The plan is to put the heavy insert session onto a ramdisk based pg-db and transfer the relevant data to the master pg-db upon session completion. Currently running 7.4.6. From what you say I'd think you want to

Re: [PERFORM] How to read query plan

2005-03-14 Thread PFC
Instead of a varchar(1) containing 'y' or 'n' you could use a BOOL or an integer. Your query seems of the form : SELECT FROM main_table LEFT JOIN a lot of tables ORDER BY sort_key LIMIT N OFFSET M; I would suggest to rewrite it in a simpler way : instead of generating the whole result

Re: [PERFORM] Avoiding tuple construction/deconstruction during joining

2005-03-15 Thread PFC
I have asked him for the data and played with his queries, and obtained massive speedups with the following queries : http://boutiquenumerique.com/pf/miroslav/query.sql http://boutiquenumerique.com/pf/miroslav/query2.sql http://boutiquenumerique.com/pf/miroslav/materialize.sql Note that my

Re: [PERFORM] Avoiding tuple construction/deconstruction during joining

2005-03-15 Thread PFC
On my machine (Laptop with Pentium-M 1.6 GHz and 512MB DDR333) I get the following timings : Big Joins Query will all the fields and no order by (I just put a SELECT * in the first table) yielding about 6k rows : = 12136.338 ms Replacing the SELECT * from the table with many fields by

Re: [PERFORM] Speeding up select distinct

2005-03-16 Thread PFC
Try : SELECT owner from pictures group by owner; Any ideas, apart from more or less manually maintaining a list of distinct owners in another table ? That would be a good idea too for normalizing your database. ---(end of

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-19 Thread PFC
This is really great ! Think about altering the partitioning (this is quite complex) : imagine a table split in several partitions archive and current where a row is moved from current to archive when it will not be updated anymore. Sometimes you can partition on a simple numeric value,

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-19 Thread PFC
tableoid would accomplish that already, assuming that the partitioned table is effectively a view on separate physical tables. regards, tom lane Very good. Also note the possibility to mark a partition READ ONLY. Or even a table. It does not seem very useful but just think that for

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread PFC
It would also be reasonable to allow clustering individual partitions; creating table or column constraints on some partitions and not others; I have a session mamagement which works like that, using views now. sessions.online is a table of the online sessions. It has a UNIQUE on user_id.

Re: [PERFORM] best practices with index on varchar column

2005-03-22 Thread PFC
Can I use an index on a varchar column to optimize the SELECT queries that use column LIKE 'header%' ? Yes If yes what is the best tree algotithm to use ? Btree Note that if you want case insensitive matching you need to make an index on lower(column) and SELECT WHERE lower(column)

Re: [PERFORM] How to improve db performance with $7K?

2005-03-29 Thread PFC
With hardware tuning, I am sure we can do better than 35Mb per sec. Also WTF ? My Laptop does 19 MB/s (reading 10 KB files, reiser4) ! A recent desktop 7200rpm IDE drive # hdparm -t /dev/hdc1 /dev/hdc1: Timing buffered disk reads: 148 MB in 3.02 seconds = 49.01 MB/sec # ll

Re: [PERFORM] Dynamic query perormance

2005-03-30 Thread PFC
which is evaluated and replanned on each entry to the FOR loop. This allows the programmer to choose the speed On each entry is not the same as on each iteration. It would means every time the loop is started... Regards, PFC ---(end

Re: [PERFORM] fine tuning for logging server

2005-03-31 Thread PFC
The reason: if the power cord is yanked, the OS _must_ boot back up in good condition. If the DB is corrupted, whatever, nuke it then re- initialize it. But the OS must survive act-of-god events. Well, in that case : - Use reiserfs3 for your disks - Use MySQL with MyISAM

Re: [PERFORM] Very long time to execute and Update, suggestions?

2005-03-31 Thread PFC
So I need to either find an Index that will work with regexes or re-write my code to update each subdirectory separately and use simpler UPDATE statement for each. Why don't you use a LTREE type to model your directory tree ? It's been designed specifically for this purpose and has

Re: [PERFORM] Query Optimizer Failure / Possible Bug

2005-04-03 Thread PFC
Noticed this problem,too. You can always make the calculation you want done once inside a set returning function so it'll behave like a table, but that's ugly. On Mon, 28 Mar 2005 16:14:44 +0200, Hannes Dorbath [EMAIL PROTECTED] wrote: hm, a few days and not a single reply :| any more

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread PFC
This is done using COPY syntax, not INSERT syntax. So I suppose yes I do. The file that is being used for COPY is kept on a ramdisk. COPY or psql \copy ? If you wanna be sure you commit after each COPY, launch a psql in a shell and check if the inserted rows are visible (watching SELECT

Re: [PERFORM] Follow-Up: How to improve db performance with $7K?

2005-04-06 Thread PFC
and we get about 65MB/sec sustained when writing to an ext3 filesystem (actually, when wgetting a file off the gigabit LAN :-) ). I haven't Well, unless you have PCI 64 bits, the standard PCI does 133 MB/s which is then split exactly in two times 66.5 MB/s for 1) reading from the PCI network

Re: [PERFORM] Functionscan estimates

2005-04-09 Thread PFC
But with all due respect to Joe, I think the reason that stuff got trimmed is that it didn't work very well. In most cases it's *hard* to write an estimator for a SRF. Let's see you produce one for dblink() for instance ... Good one... Well in some cases it'll be impossible, but suppose I

Re: [PERFORM] Functionscan estimates

2005-04-09 Thread PFC
My solution would be a lot simpler, since we could simply populate pg_proc.proestrows with 1000 by default if not changed by the DBA. In an even better world, we could tie it to a table, saying that, for example, proestrows = my_table*0.02. What if the estimated row is a function of a

Re: [PERFORM] Foreign key slows down copy/insert

2005-04-14 Thread PFC
I have a table A with an int column ID that references table B column ID. Table B has about 150k rows, and has an index on B.ID. When trying to copy 1 million rows into A, I get the following \timings: You're using 7.4.5. It's possible that you have a type mismatch in your foreign keys which

Re: [PERFORM] How to improve db performance with $7K?

2005-04-14 Thread PFC
The real question is whether you choose the single 15kRPM drive or additional drives at 10kRPM... Additional spindles would give a much bigger And the bonus question. Expensive fast drives as a RAID for everything, or for the same price many more slower drives (even SATA) so you can put the

Re: [PERFORM] How to improve db performance with $7K?

2005-04-15 Thread PFC
My argument is that a sufficiently smart kernel scheduler *should* yield performance results that are reasonably close to what you can get with that feature. Perhaps not quite as good, but reasonably close. It shouldn't be an orders-of-magnitude type difference. And a controller card (or

Re: [PERFORM] How to improve db performance with $7K?

2005-04-15 Thread PFC
platter compared to the rotational speed, which would agree with the fact that you can read 70MB/sec, but it takes up to 13ms to seek. Actually : - the head has to be moved this time depends on the distance, for instance moving from a cylinder to the next is very fast (it needs to, to get

Re: [PERFORM] Foreign key slows down copy/insert

2005-04-15 Thread PFC
Index Scan using ix_B on B (cost=0.04..3.06 rows=1 width=329) (actual time=93.824..93.826 rows=1 loops=1) Index Cond: (id = $0) InitPlan - Limit (cost=0.00..0.04 rows=1 width=4) (actual time=15.128..15.129 rows=1 loops=1) - Seq Scan on A (cost=0.00..47569.70

Re: [PERFORM] BLOB's bypassing the OS Filesystem for better Image

2005-05-01 Thread PFC
My laptop reads an entire compiled linux kernel (23000 files totalling 250 MBytes) in about 1.5 seconds if they're in cache. It's about 15.000 files/second. You think it's slow ? If you want to read them in random order, you'll probably use something else than a laptop drive, but you get

Re: [PERFORM] Partitioning / Clustering

2005-05-10 Thread PFC
SELECT row1, row2 FROM table1_on_machine_a NATURAL JOIN table2_on_machine_b WHERE restrict_table_1 AND restrict_table_2 AND restrict_1_based_on_2; I don't think that's ever going to be efficient... What would be efficient would be, for instance, a Join of a part of a table against another

Re: [PERFORM] Partitioning / Clustering

2005-05-11 Thread PFC
However, memcached (and for us, pg_memcached) is an excellent way to improve horizontal scalability by taking disposable data (like session information) out of the database and putting it in protected RAM. So, what is the advantage of such a system versus, say, a sticky sessions system

Re: [PERFORM] BLOB's bypassing the OS Filesystem for better Image loading speed?

2005-05-12 Thread PFC
Filesystems with many Filesystem Objects can slow down the Performance at opening and reading Data. On my laptop, lighttpd takes upto 15000 hits PER SECOND on static 2-3 Kb files (tested with apachebench 2). Apache is slower, of course : 3-4000 hits per second which is not that bad. Using

Re: [PERFORM] Partitioning / Clustering

2005-05-12 Thread PFC
machines. Which has it's own set of issues entirely. I am not entirely sure that memcached actually does serialize data when it's comitted into I think it does, ie. it's a simple mapping of [string key] = [string value]. memcached either, although I could be wrong, I have not looked at the

Re: [PERFORM] Partitioning / Clustering

2005-05-12 Thread PFC
100 hits a second = 8,640,000 hits a day. I work on a site which does 100 million dynamic pages a day. In comparison Yahoo probably does 100,000,000,000 (100 billion) views a day if I am interpreting Alexa's charts correctly. Which is about 1,150,000 a second. Read the help on

Re: [PERFORM] Partitioning / Clustering

2005-05-14 Thread PFC
If you make the assertion that you are transferring equal or less session data between your session server (lets say an RDBMS) and the app server than you are between the app server and the client, an out of band 100Mb network for session information is plenty of bandwidth. So if you count on a

Re: [PERFORM] where+orderby+limit not (always) using appropriate index?

2005-05-18 Thread PFC
SELECT o FROM t WHERE i = 1 ORDER BY o LIMIT 1; use : ORDER BY i, o If you have a multicol index and want to order on it, you should help the planner by ORDERing BY all of the columns in the index... It bit me a few times ;) ---(end of

Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-05-24 Thread PFC
It's common knowledge, it seems, that MySQL without transactions will be a lot faster than Postgres on Inserts. And on Updates too, that is, unless you have more than a few concurrent concurrent connections, at which point the MySQL full table lock will just kill everything. And you don't

Re: [PERFORM] Select performance vs. mssql

2005-05-24 Thread PFC
Pretty much. There has been discussion about allowing index-only access to frozen tables, i.e. archive partitions. But it all sort of hinges on someone implementing it and testing Would be interesting as a parameter to set at index creation (ie. if you know this table will have a

Re: [PERFORM] OID vs overall system performances on high load

2005-05-28 Thread PFC
The OID column is an extra few bytes on each row. If you don't have any use for it (and let's face it: most of us don't), then create your tables without OID. Also there are some useful hacks using the oid which don't work if it wraps around, thus preventing it from wrapping around by

Re: [PERFORM] Query plan for very large number of joins

2005-06-02 Thread PFC
I am using PostgreSQL (7.4) with a schema that was generated automatically (using hibernate). The schema consists of about 650 relations. One particular query (also generated automatically) consists of left joining approximately 350 tables. At this Just out of curiosity, what

Re: [PERFORM] strategies for optimizing read on rather large tables

2005-06-04 Thread PFC
select advert_id from acr_cache where category_id = ? and region_id = ? order by XXX {asc|desc} limit 20; where XXX is one of 5 possible fields, timestamp, timestamp, text, text, numeric Create 5 indexes on ( category_id, region_id, a field ) where a field is one of your 5

Re: [PERFORM] strategies for optimizing read on rather large tables

2005-06-04 Thread PFC
select advert_id from acr_cache where category_id = ? and region_id = ? order by category_id, region_id, XXX limit 20; don't forget to mention all the index columns in the order by, or the planner won't use it. ---(end of broadcast)--- TIP

Re: [PERFORM] SELECT DISTINCT Performance Issue

2005-06-06 Thread PFC
Previously, we have also tried to use LIMIT 1 instead of DISTINCT, but the performance was no better: select PlayerID,AtDate from Player where PlayerID='0' order by PlayerID desc, AtDate desc LIMIT 1 The DISTINCT query will pull out all the rows and keep only one, so the one with

Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-06 Thread PFC
If you want something more embedded in your application, you could consider : http://firebird.sourceforge.net/ http://hsqldb.sourceforge.net/ http://sqlite.org/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose

Re: [PERFORM] Performance nightmare with dspam (urgent) (resolved)

2005-06-06 Thread PFC
PostgreSQL and say to use MySQL if you want reasonable performance. If you want MySQL performance and reliability with postgres, simply run it with fsync deactivated ;) I'd suggest a controller with battery backed up cache to get rid of the 1 commit = 1 seek boundary. Makes it real

Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-06 Thread PFC
postgres - returns results in 2.8 seconds What kind of plan does it do ? seq scan on the big tables and hash join on the small tables ? mysql - takes around 16 seconds (This is with myisam ... with innodb it takes 220 seconds) I'm not surprised at all. Try the same

Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-06 Thread PFC
Please pardon my ignorance, but from whatever I had heard, mysql was supposedly always faster than postgres Thats why I was so surprised !! I heard a lot of this too, so much it seems common wisdom that postgres is slow... well maybe some old version was, but it's getting better at

Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-07 Thread PFC
My tests included using aqua studios connection to both databases and .asp page using odbc connections. Performance also depends a lot on the driver. For instance, the PHP driver for MySQL is very very fast. It is also very dumb, as it returns everything as a string and doesn't

Re: [PERFORM] How does the transaction buffer work?

2005-06-16 Thread PFC
transaction, delete everything and then just dump new data in (copy perhaps). The old data would be usable to other transactions until I commit my insert. This would be the fastest way, but how much memory would this use? Will this cause performance issues on a heavily loaded server with too

Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster

2005-06-18 Thread PFC
I do not know what clustering would do for you. But striping will provide a high level of assurance that each of your hard drives will process equivalent amounts of IO operations. I don't know what I'm talking about, but wouldn't mirorring be faster than striping for random reads

Re: [PERFORM] Do Views execute underlying query everytime ??

2005-06-21 Thread PFC
However, I donot know if the query that creates the view is executed everytime I select something from the view. Because if that is the case, then I think my queries will again be slow. But if that is the way views work, then what would be the point in creating them .. Views are more for

Re: [PERFORM] Do Views execute underlying query everytime ??

2005-06-21 Thread PFC
From what you say I understand that you have a huge table like this : ( name, value, id ) And you want to make statistics on (value) according to (name,id). *** First of all a materialized view doen't exist in postgres, it's just a word to

Re: [PERFORM] Querying 19million records very slowly

2005-06-21 Thread PFC
use CURRENT_TIME which is a constant instead of now() which is not considered constant... (I think) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] Too slow querying a table of 15 million records

2005-06-28 Thread PFC
database= explain select date_trunc('hour', time),count(*) as total from test where p1=53 and time now() - interval '24 hours' group by date_trunc order by date_trunc ; 1. Use CURRENT_TIMESTAMP (which is considered a constant by the planner) instead of now() 2. Create a multicolumn

Re: [PERFORM]

2005-07-07 Thread PFC
So, it seems that for my application (database in memory, 14 millions of very small requests), Centrino (aka Pentium M) has a build-in hardware to boost Postgres performance :-) Any experience to confirm this fact ? On my Centrino, Python flies. This might be due to the very large

Re: [PERFORM] Need suggestion high-level suggestion on how to solve a performance problem

2005-07-07 Thread PFC
Hello, I once upon a time worked in a company doing backup software and I remember these problems, we had exactly the same ! The file tree was all into memory and everytime the user clicked on something it haaad to update everything. Being C++ it was very fast, but to backup a

Re: [PERFORM] Need suggestion high-level suggestion on how to solve a performance problem

2005-07-07 Thread PFC
This is the kicker right there; my program is released under the GPL so it's fee-free. I can't eat anything costly like that. As it is there is hundreds and hundreds of hours in this program that I am already hoping to recoup one day through support contracts. Adding commercial

Re: [PERFORM] slow joining very large table to smaller ones

2005-07-15 Thread PFC
Ok, I tried this one. My ssh keeps getting cut off by a router somewhere between me and the server due to inactivity timeouts, so all I know is that both the select and explain analyze are taking over an hour to run. Here's the explain select for that one, since that's the best I can get.

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC
What happens if, say at iteration 6000 (a bit after the mess starts), you pause it for a few minutes and resume. Will it restart with a plateau like at the beginning of the test ? or not ? What if, during this pause, you disconnect and reconnect, or restart the postmaster, or vacuum, or

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC
total. If the insertion pattern is sufficiently random that the entire index ranges are hot then you might not have enough RAM. Try doing the test dropping some of your indexes and see if it moves the number of iterations after which it becomes slow. ---(end of

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC
I think PFC's question was not directed towards modeling your application, but about helping us understand what is going wrong (so we can fix it). Exactly, I was wondering if this delay would allow things to get flushed, for instance, which would give information about the problem (if

Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC
What programming language are these scripts written in ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC
It is a subprocess of a Python process, driven using a pexpect interchange. I send the COPY command, then wait for the '=#' to come back. did you try sending the COPY as a normal query through psycopg ? ---(end of broadcast)--- TIP 9:

Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC
I can't say wether MySQL is faster for very small queries (like SELECT'ing one row based on an indexed field). That's why I was asking you about the language... I assume you're using a persistent connection. For simple queries like this, PG 8.x seemed to be a lot faster

Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC
PS: Where can I find benchmarks comparing PHP vs Perl vs Python in terms of speed of executing prepared statements? I'm afraid you'll have to do these yourself ! And, I don't think the Python drivers support real prepared statements (the speed of psycopy is really good though). I

Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC
You could have a program pre-parse your log and put it in a format understandable by COPY, then load it in a temporary table and write a part of your application simply as a plpgsql function, reading from this table and doing queries (or a plperl function)... So... (bear with me here..

Re: [PERFORM] Planner doesn't look at LIMIT?

2005-07-22 Thread PFC
Which row do you want ? Do you want 'a row' at random ? I presume you want the N latest rows ? In that case you should use an ORDER BY on an indexed field, the serial primary key will do nicely (ORDER BY id DESC) ; it's indexed so it will use the index and it will fly.

Re: [PERFORM] Cheap RAM disk?

2005-07-26 Thread PFC
I'm a little leary as it is definitely a version 1.0 product (it is still using an FPGA as the controller, so they were obviously pushing to get the card into production). Not necessarily. FPGA's have become a sensible choice now. My RME studio soundcard uses a big FPGA. The performance

Re: [Bizgres-general] Re: [PERFORM] faster INSERT with possible

2005-07-27 Thread PFC
I had in mind the extra tables that an application sometimes needs to operate faster. Denormalisations, pre-joined tables, pre-calculated results, aggregated data. These are not temporary tables, just part of the application - multi-user tables that stay across shutdown/restart. You could

Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0

2005-07-29 Thread PFC
Also, this test goes a bit faster with sync turned off, if mysql isn't using sync that would be why it's so much faster. Anyone know what the default for mysql is? For InnoDB I think it's like Postgres (only slower) ; for MyISAM it's no fsync, no transactions, no crash tolerance of any

Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0

2005-08-04 Thread PFC
What I really want to do is have the token array available as a record so that I can query against it, but not have it take up the resources of a real table. If I could copy from an array into a record then I can even get rid of the loop. Anyone have any thoughts on how to do this? You

Re: [PERFORM] partial index regarded more expensive

2005-08-10 Thread PFC
why not simply create an index on (game_end, state) ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Speedier count(*)

2005-08-11 Thread PFC
You could lock the count table to prevent the problem where 2 competing transactions do an insert, read the start value and add 1 to it and then write the result - which is n+1 rather then n+2 - so you are off by one. Think of the same when one transaction inserts 100 and the other 120. Then

Re: [PERFORM] Caching by Postgres

2005-08-23 Thread PFC
Josh Berkus has already mentioned this as conventional wisdom as written by Oracle. This may also be legacy wisdom. Oracle/Sybase/etc has been around for a long time; it was probably a clear performance win way back when. Nowadays with how far open-source OS's have advanced, I'd take it

Re: [PERFORM] Read/Write block sizes

2005-08-24 Thread PFC
of effort reinventing the wheel ... but our time will be repaid much more if we work at levels that the OS cannot have knowledge of, such as join planning and data statistics. Considering a global budget of man-hours which is the best ? 1- Spend it on reimplementing half of VFS in

Re: [PERFORM] Caching by Postgres

2005-08-24 Thread PFC
Really? Cool, I'd like to see that. Could you follow up with Hans? Or give me his e-mail? You can subscribe to the Reiser mailinglist on namesys.com or : [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading

Re: [PERFORM] Caching by Postgres

2005-08-24 Thread PFC
At least on Sparc processors, v8 and newer, any double precision math (including longs) is performed with a single instruction, just like for a 32 bit datum. Loads and stores of 8 byte datums are also handled via a single instruction. The urban myth that 64bit math is

Re: [PERFORM] Advise about how to delete entries

2005-09-02 Thread PFC
DELETE FROM statistics_sasme WHERE statistic_id = 9832; As Michael said, why use a NUMERIC when a bigint is faster and better for your use case, as you only need an integer and not a fixed precision decimal ? Also if you use postgres 8, the index will not be used if you search on

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

2005-09-12 Thread PFC
I know I haven't provided a whole lot of application-level detail here, You did ! What about : - using COPY instead of INSERT ? (should be easy to do from the aggregators) - using Bizgres ? (which was designed for your

[PERFORM] Advice on RAID card

2005-09-24 Thread PFC
Hello fellow Postgresql'ers. I've been stumbled on this RAID card which looks nice. It is a PCI-X SATA Raid card with 6 channels, and does RAID 0,1,5,10,50. It is a HP card with an Adaptec chip on it, and 64 MB cache. HP Part # : 372953-B21 Adaptec Part # :

Re: [PERFORM] Advice on RAID card

2005-09-24 Thread PFC
It looks like a rebranded low end Adaptec 64MB PCI-X - SATA RAID card. Looks like the 64MB buffer is not upgradable. Looks like it's SATA, not SATA II Yeah, that's exactly what it is. I can get one for 150 Euro, the Areca is at least 600. This is for a budget server so while it would be

Re: [PERFORM] Advice on RAID card

2005-09-25 Thread PFC
The common explanation is that CPUs are so fast now that it doesn't make a difference. From my experience software raid works very, very well. However I have never put software raid on anything that is very heavily loaded. Even for RAID5 ? it uses a bit more CPU for the parity

Re: [PERFORM] Advice on RAID card

2005-09-25 Thread PFC
There is a huge advantage to software raid on all kinds of levels. If you have the CPU then I suggest it. However you will never get the performance out of software raid on the high level (think 1 gig of cache) that you would on a software raid setup. It is a bit of a tradeoff but for most

Re: [PERFORM] Comparative performance

2005-09-29 Thread PFC
It appears that PostgreSQL is two to three times slower than MySQL. For example, some pages that have some 30,000 characters (when saved as HTML) take 1 to 1 1/2 seconds with MySQL but 3 to 4 seconds with PostgreSQL. I had read that the former was generally faster than the latter,

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-29 Thread PFC
Just to add a little anarchy in your nice debate... Who really needs all the results of a sort on your terabyte table ? I guess not many people do a SELECT from such a table and want all the results. So, this leaves : - Really wanting all the results, to fetch using

Re: [PERFORM] Comparative performance

2005-09-29 Thread PFC
Total runtime: 16.000 ms Even though this query isn't that optimized, it's still only 16 milliseconds. Why does it take this long for PHP to get the results ? Can you try pg_query'ing this exact same query, FROM PHP, and timing it with getmicrotime() ? You can even do an

  1   2   3   >