Re: [PERFORM] Poor performance of group by query

aggregate. It's a pretty gross amount of time for 18k records. There was a thought a while back about making 64-bit machines handle 64-bit datatypes like bigints without pointers. That would help on your Opteron. -- greg ---(end of broadcast)

Re: [PERFORM] Horribly slow hash join

he low 32 bits. That maintains the property you need and at least ensures that all the bits are taken into account. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Horribly slow hash join

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

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

ts postgres is missing out on by using its own hand-rolled locking instead of using the pthreads infrastructure that the kernel is often involved in. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Horribly slow hash join

ment somewhere. http://burtleburtle.net/bob/hash/doobs.html Incidentally, this text claims mod is extremely slow compared to bit manipulations. I don't know that that kind of cycle counting is really is a factor for postgres though. Also, incidentally, this text is interesting: http://www.

Re: [PERFORM] Horribly slow hash join

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

Re: [PERFORM] Horribly slow hash join

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 comm

Re: [PERFORM] Clarification on some settings

read on Linux and swapping, you can find out more here: http://kerneltrap.org/node/view/3080. Cheers! -- Greg Copeland, Owner [EMAIL PROTECTED] Copeland Computer Consulting 940.206.8004 ---(end of broadcast)--- TIP 2: you can get off all

Re: [PERFORM] cache table

you could use something like memcached (http://www.danga.com/memcached/). That might be your best fit for how you describe your requirements. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Horribly slow hash join

to generate a nice distribution. That doesn't really guarantee anything though, except that those common values would at least be well distributed to start with. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] linux distro for better pg performance

This is where RAID5 got its bad reputation. Good modern RAID5 controllers can minimize this problem but I think it's still an issue for a lot of lower end hardware. I wonder if postgres's architecture might minimize it already just because of the pattern of writes it generates. --

Off Topic - Re: [PERFORM] Quad processor options - summary

o the list with the original problem included and all solutions found. Also makes searching the list archives easier. Simply a suggestion for us all including myself. Greg Bjoern Metzdorf wrote: Hi, at first, many thanks for your valuable replies. On my quest for the ultimate hardware platform I

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

t to be slower than an emulator. Barring effects like the disk caching I mentioned, it should far outpace the emulators. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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

e the entire 2GB file or even very much of it. It just has to store the block that linux wants to write and report success to linux without waiting for the disk to report success. Linux will then think the file is sync'd to disk and allow postgres to continue with the

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

more or faster processors. If you benchmark your application on a given set of hardware and analyze where your bottleneck is then people may be able to suggest what alternatives to consider and may be able to give some idea of what type of improvement to expect. But it takes act

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

our table which is confusing the sampling. If that's it, then yes it's known and in fact already improved in what will be 7.5. You may be able to avoid the situation by vacuuming more frequently. If that doesn't solve it then I would suggest trying to raise the statistics targets for

Re: [PERFORM] Use of Functional Indexs and Planner estimates

l the planner not to expect more than 100 rows and to take the plan likely to produce the first 100 rows fastest. But this has the disadvantage of uglifying your code and introducing an arbitrary limit. When 7.5 comes out it you'll want to rip this out. -- greg ---(end

Re: [PERFORM] Performance over a LAN

t be hard to decode the postgres protocol though. Which driver are you using? I wonder if it isn't the same nagle+delayed ack problem that came up recently. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] arrays and indexes

ST indexes and the operators from the contrib/intarray directory from the Postgres source. However I warn you in advance that this is fairly esoteric stuff and will take some time to get used to. And at least in my case I found the indexes didn't actually help much for my data sets, probably

Re: [PERFORM] arrays and indexes

curious to know how it goes. My own project uses denormalized sets stored as arrays as well, though in my case they're precalculated from the fully normalized data. I tried to use GiST indexes but ran into problems combining the btree-GiST code with array GiST code in a multicolumn index.

Re: [PERFORM] arrays and indexes

i/o to read all those indexes would probably be a net loss when they push other things out of cache. I could try setting up a test case, but I think all it took was having a btree-gist index that was insufficiently selective. In my case I had about 900 integer values each

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

for multi-column keys. It seems it would be nice if some syntax similar to (a,b,c) > (a1,b1,c1) worked for this. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

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

hey use "x" alone) That fairly clearly specifies (a,b,c) < (a1,b1,c1) to work the way you want it to. Less-than-or-equal is then defined based on the above definition. Even if Postgres did this right I'm not sure that would solve your index woes. I imagine the first thing Postgres w

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

AND_EXPR; } 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. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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

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

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

t ask in the other email. In the midst of a forboth() loop, how would I tell if I'm at the last element of the lists? Would lnext(l)==NULL do it? -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an approp

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

that issue on the mailing list previously. In that case though, it seems even the existing code is insufficient. Instead of testing whether the operator with strcmp against "=" and "<>" it should perhaps be looking for an operator class and the strategy number for the opera

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

> I'll just troubleshoot specific cases via application specific behavior > as they come up. In any case, many thanks to Greg and Tom for taking > the time to pick this apart. Well I'm not sure whether you caught it, but Tom did come up with a work-around that works with the curren

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

text. Notably integers don't. "10" sorts before "2" for example. You could probably deal with this with careful attention to each datatype you're converting if you're interested in going to that length. -- greg ---(end of broadcast)--

[PERFORM] SSD Drives

us SSD PCI cards are no more, Platypus appears to have gone out of business. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

roved to maintain the clustering continuously, and an actual index-organized-table where the tuples are actually only stored in a btree structure. They're two different approaches to similar problems. But they might both be useful to have, and have markedly different implementation details

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

ld make it more feasible to use a pctfree parameter to make clustering more effective. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

up. If it doesn't fit on the page the worst that happens is it has to store it on some other page, whereas oracle has to do its strange row chaining thing. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

em and get the performance gain. The tables that would benefit from this would be tables always accessed by indexes in index scans of more than one record. The better the clustering the fewer pages the index scan would have to read in. If the data took 10% more space but the index scan only needs 1

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

res are you using. . The output of EXPLAIN ANALYZE select ... -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

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

er calculate that without doing a sequential scan when count(*) would force it to do a sequential scan? -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

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

ese common cases end up making it a good idea to read the records into the clients and do the work in the client. The only cases where that would make sense would be if the function requires doing some manipulation of the data that's awkward to express in sql. The "top n" type

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

uld when I upgrade to 8.0 have to ensure that my application driver is either not using placeholders at all (at the protocol level -- I always prefer them at the api level) or ensure that postgres is *not* peeking at the value. I like the feature but I just want to be sure that it's optional.

Re: [PERFORM] seqscan instead of index scan

se the index: select * from t where c>0 and d>0 order by name Could scan the index and not even have to sort on name. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

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

Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > However I have the complementary reaction. I find peeking at the first > > bind parameter to be scary as hell. Functions seem slightly less scary. > > FWIW, we only do it in the

Re: [PERFORM] Multiple Uniques

m not sure why this is any faster since it still has to do all the same work, but it's a different code path and it seems to be about 20% faster for me. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Multiple Uniques

s_parameters(); $query = "select $selected_columns where id IN ($accessible_ids)" In an ideal world functionally equivalent queries should always generate identical plans. Of course there are limitations, it's not an ideal world, but as much as possible it should be possible to write

Re: [PERFORM] Partitioning

It can also do some magic things with "GROUP BY partition_key" and "ORDER BY partition_key". The work in the optimizer will be the most challenging part. In an ideal world if the optimizer is very solid it will be possible to bring some partitions to slow or even ne

Re: [PERFORM] Comparing user attributes with bitwise operators

dapt it to apply to bit fields. If so I think it would be a useful tool. But GiST indexing is pretty esoteric stuff. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Comparing user attributes with bitwise operators

or writing all kinds of queries against. But like you've found, it's hard to optimize this to be fast enough for transactional use. I think the normal approach with dating sites is to leave this for a batch job that populates a match table for everyone and just have the

Re: [PERFORM] Comparing user attributes with bitwise operators

You'll have to test this carefully. I tried using GiST indexes for my project and found that I couldn't load the data and build the GiST indexes fast enough. You have to test the costs of building and maintaining this index, especially since it has so many columns in it. But it looks like your queries are in trouble without it so hopefully it'll be ok on the insert/update side for you. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

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

kup_tipo_evento le (cost=0.00..1.16 > rows=16 width=32) (never executed) 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 sk

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

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

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

* current batch #, or 0 during 1st pass */ + int ntup; /* Total number of tuples hashed in this batch */ + /* * all these arrays are allocated for the life of the hash join, but * only if nbatch > 0: -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

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

match. But unless you do experiments you won't know if it would be of any benefit to change. > I'm also not entirely sure how to make the datablocks line up with the > filesystem blocks. Any suggestions on this would be greatly appreciated. They just will. The files start on a

Re: [PERFORM] Caching of Queries

e, you tend to get the same pages over and over thousands of times. So if they prepare their queries the first time around they can reuse those prepared queries thousands of times. Using a text cache of the query string on the server side is just a work-around for failing to

Re: [PERFORM] Hyper threading?

where HT helps. Long story short, the general rule is, slower unless you having proven it to be faster. Cheers, -- Greg Copeland, Owner [EMAIL PROTECTED] Copeland Computer Consulting 940.206.8004 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

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

40% is unrealistic. The traditional rule of thumb for the break-even point was 10%. In POstgres the actual percentage varies based on how wide the records are and how correlated the location of the records is with the index. Usually it's between 5%-10% but it can be even lower th

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

d effectively. There are APIs to bar swapping out pages and the tests could be run without swap. I suggested it only as an experiment though, there are lots of details between here and having it be a good configuration for production use. -- greg ---

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

he long term, but it seems like slim odds now. But one way or the other having two layers of buffering seems like a waste. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Query planner problem

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)--

Re: [PERFORM] Excessive context switching on SMP Xeons

ct Postgres with its heavy demand on memory bandwidth and shared memory could potentially benefit more than usual from being able to context switch during pipeline stalls. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to

Re: [PERFORM] sequential scan on select distinct

hat the optimizer would have to make a judgement call based on the expected number of distinct values. If you had much more than 256 distinct values then the your plpgsql function wouldn't have performed well at all. -- greg ---(end of broadcast)--

Re: [PERFORM] Comparing user attributes with bitwise operators

st underestimating the size of this index and the amount of time it'll take to delete it? Or are there queries actively executing using the index while you're trying to delete it? Or a vacuum running? -- greg ---(end of broadcast)--- TIP

Re: [PERFORM] sequential scan on select distinct

Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > why isn't a "skip index scan" plan available? Well, nobody's written the code > > yet. > > I don't really think it would be a useful plan anyway. Well

Re: [PERFORM] sequential scan on select distinct

of tuples to calculate their results. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [PERFORM] Strange (?) Index behavior?

le on sname_unique. That would accomplish the same thing as the VACUUM FULL command and also speed up the index scan. And the optimizer knows (if you analyze afterwards) it so it should be more likely to pick the index scan. But currently you have to rerun cluster periodically. -- greg -

Re: [PERFORM] Some quick Opteron 32-bit/64-bit results

compression > running 2 or 3 times faster in 64-bit mode versus 32-bit. Isn't this a major kernel bump too? So a different scheduler, different IO scheduler, etc? -- greg ---(end of broadcast)--- TIP 5: Have you checked our

Re: [PERFORM] memcached and PostgreSQL

hed but I'm unclear it would it really help at all. Has anyone tried anything like this? -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] Data type to use for primary key

far as I know it hasn't happened yet though. And it's just so much more convenient having the postal code handy instead of having to join against another table to look it up. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Data type to use for primary key

Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > This could hurt if they ever reuse an old previously retired postal code, > > which isn't an entirely impossible case. As far as I know it hasn't happened > > yet thoug

Re: [PERFORM] Config review

is trying to sort a lot of data. You might have to lower sort_mem to a conservative value before you could see that though. The pgsql_tmp directory appears (and disappears?) as needed, it's something like this: bash-2.05b# ls /var/lib/postgres/data/base/17150/pgsql_tmp pgsql_tmp22

Re: [PERFORM] Partitioned table performance

reason we wanted to partition something anyways. It is handy having a higher level interface to deal with partitioned tables. You can create a single "local" or "segmented" index and not have to manually deal with all the partitions as separate tables. But that's just syntactic sugar. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Partitioned table performance

st syntactic sugar. > > Right, and the easy part. I think the hard part lies in the optimizer actually. The semantics of the operations to manipulate partitions might be tricky to get right but the coding should be straightforward. Having the optimizer be able to recognize when it can prune partitions will be a lot of work. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Seqscan rather than Index

cords in 0.5 to 0.2 times less space than > PG? I don't know what he's talking about either. Perhaps he's thinking of people who haven't been running vacuum enough? -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [PERFORM] Seqscan rather than Index

Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > Postgres is also more pessimistic about the efficiency of index scans. It's > > willing to use a sequential scan down to well below 5% selectivity when > > other > > data

Re: [PERFORM] Trying to create multi db query in one large querie

...);" & PID2=$! # wait wait $PID1 wait $PID2 # Do more on mydb.mytable ... Something like that so no guaranties. I do remember testing with this a while back and it is useful for JOIN's. Greg -Original Message- From: Hasnul Fadhly bin Hasan To: [EMAIL PROTECTED] Sent: 12/1

Re: [PERFORM] Wrong Stats and Poor Performance

find out what fsm settings you need. But I don't remember which number you should be looking at there offhand. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Hardware purchase question

ler so those reads are mostly cached and the extra writes don't saturate the i/o throughput. But those kinds of controllers are expensive and not an option for software raid. -- greg ---(end of broadcast)--- TIP 3: if posting/reading thr

Re: [PERFORM] Low Performance for big hospital server ..

Frank Wiles <[EMAIL PROTECTED]> writes: > Now that's rich. I don't think I've ever seen a database perform > worse after it was normalized. In fact, I can't even think of a > situation where it could! Just remember. All g

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

in 1U would be damned hard to cool effectively, no? -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get th

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

ntial reads. Incidentally, the same was true for Oracle on Solaris. If we found excessive cpu use typically meant some frequently executed query was using a sequential scan on a small table. Small enough to fit in RAM but large enough to consume lots of cycles reading it. -- greg

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

to fit in RAM but large enough to consume a significant amount of cpu, especially with the query being run thousands of times per minute. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

y drive fails you only lose that drive from the stripe set. If another drive fails then you're ok as long as it isn't the specific drive that was paired with the first failed drive. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

disable the write caching entirely? I don't recall, did someone have a program that tested the write latency of a drive to test this? -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Swapping on Solaris

ng both top and vmstat. When you're happy with it, turn swap back on for those "heavy" load times and move on. Greg -- Greg Spiegelberg Product Development Manager Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Technolog

Re: [PERFORM]

a full table scan without the disadvantage of having to read irrelevant tuples. Postgres is sometimes going to be forced to either do a much slower index scan or read tables that aren't relevant. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

multiple machines. If you spread the load in an intelligent way you can even concentrate each server on certain subsets of the data. But that shouldn't even really be necessary, just a nice improvement. -- greg ---(end of broadcast)--- TIP

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

ill always come to the same postgres session and has some garbage collection if the user disappears. And it means the URL is only good for a limited amount of time. If they bookmark it it'll break if they come back the next day.) -- greg ---(end of broadcast)

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

Isn't this a prime example of when to use a servlet or something similar in function? It will create the cursor, maintain it, and fetch against it for a particular page. Greg -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:21

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

ypes of queries you need to perform, how many data they need to manipulate, and what your performance requirements are for those queries. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate su

Re: [PERFORM] inheritance performance

n't slowed down by the records in A. It's slightly slower because it is an inherited table, but that impact is the same regardless of what other tables inherit from the same parent and how many records are in them. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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

This might make the actual performing of the initial query much slower though since you have to wait for the entire query to be performed and the records stored. You'll also have to deal with vacuuming this table aggressively. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] inheritance performance

ken <[EMAIL PROTECTED]> writes: > On Fri, 2005-01-21 at 08:14, Greg Stark wrote: > > ken <[EMAIL PROTECTED]> writes: > > > > > >From my understanding, all the data for these columns in all the child > > > tables will be stored in this one parent t

Re: [PERFORM] inheritance performance

or something like that. It made a small but noticeable difference in the runtime but nothing that made the technique infeasible. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

urce of contention on multi-processor machines running lots of concurrent update/deletes. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

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

vantage if the data were stored in a temporary table marked as not having to be WAL logged. Instead it could be automatically cleared on every database start. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archiv

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

t of work. All you can do is shift around *when* that work is done. There's not going to be any way to avoid doing the work entirely. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

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

--- Nested Loop (cost=0.00..6.40 rows=1 width=726) -> Subquery Scan x (cost=0.00..3.18 rows=1 width=4) -> Index Scan using idx_cache on cache (cost=0.00..3.17 rows=1 width=30) Index Cond: (cache_id = 1) -> Index Scan using foo_pkey on foo (cost=0.

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

27;s not a caching artifact; the whole table is cached for both trials) -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

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

r than the C implementation. For completeness, here are the fastest times I get after repeating a few times each: 13.97 ms contrib/intagg C implementation 194.76 ms aggregate using array_append 723.15 ms aggregate with SQL state function -- greg --

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

se to the same performance as clustering. The actual cost of replicating the unused data is slight. And the simplicity of master-slave makes it much more appealing than full on clustering. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] query produces 1 GB temp file

yzing. Dirk Lutzebaeck <[EMAIL PROTECTED]> writes: > Can some please explain why the temp file is so huge? I understand > there are a lot of rows. Well that I can't explain. 22k rows of width 1361 doesn't sound so big to me either. The temporary table does need to store t

Re: [PERFORM] query produces 1 GB temp file

disk sort algorithm was the polyphase tape sort from Knuth which is always reading two tapes and writing to a third. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] query produces 1 GB temp file

ave a more difficult problem. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

<    1   2   3   4   5   6   7   8   9   10   >