Re: [GENERAL] Slow PITR restore

maximum bandwidth the hard drive can handle. Even that single drive can probably handle 60MB/s sequential I/O. That's probably the source of the unrealistic expectations people have. It's easy to watch the bandwidth number as the headline number to measure i/o utiliz

Re: [GENERAL] Slow PITR restore

"Simon Riggs" <[EMAIL PROTECTED]> writes: > On Thu, 2007-12-13 at 06:27 +, Gregory Stark wrote: >> Heikki proposed a while back to use posix_fadvise() when processing logs to >> read-ahead blocks which the recover will need before actually attempting to >&g

Re: [GENERAL] Hash join in 8.3

an 8.1.9 (I think 8.1.10 came out with some planner fixes btw) and so it's deciding these other plans are better. And they might have been better for the imaginary scenario that the planner thinks is going on. -- Gregory Stark EnterpriseDB http://www.enterp

Re: [GENERAL] Hash join in 8.3

André Volpato <[EMAIL PROTECTED]> writes: > Gregory Stark escreveu: >> André Volpato <[EMAIL PROTECTED]> writes: >> >> I think the answer is that if you have bad statistics you'll get a bad plan >> and which bad plan is going to be pretty much random

Re: [GENERAL] Hash join in 8.3

Tricky! > > I wont trust table vacuums anymore... > > -- HTML-only mail isn't looked upon too favourably here. You keep saying "vacuum" which makes me think maybe you're not actually analyzing your tables at all. "vacuum" doesn't analyze the tables

Re: [GENERAL] Planner ignoring to use INDEX SCAN

what the planner thought was > going to happen. > > Its from EXPLAIN ANALYSE No, it's not. Not unless you've removed all the added info explain analyse adds to the explain output. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about Enterprise

Re: [GENERAL] mssql migration and boolean to integer problems

11:31:00.000','2007-12-13 11:31:00.000',2,2,NULL); There's an SQL standard syntax too, but the Postgres-specific syntax is: postgres=# select 1::bool; bool -- t (1 row) postgres=# select 0::bool; bool -- f (1 row) Alternatively you could just quote th

Re: [GENERAL] size cost for null fields

he null bitmap itself. If you have *any* null fields then you pay one bit for *every* field plus alignment to a 4-byte boundary. Except there's one available free byte so if you have 8 or fewer fields even that is entirely free. -- Gregory Stark EnterpriseDB http://www.enter

Re: [GENERAL] Way to avoid expensive Recheck Cond in index lookup?

very large arrays. So I'm sure xpath() could possibly trigger the same case. But the number of matching pages would have to be quite large. And in that case the alternative (regular index scans) is going to suck too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com

Re: [GENERAL] Problem with collector statistic

5..536.761 rows=55204 loops=1)" That doesn't match the query you describe. Notably there's no LIMIT in the query (or the other plan) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication suppo

Re: [GENERAL] A counter argument about DISTINCT and GROUP BY in PostgreSQL

se of you who are curious, in mysql (that other db), this > would be: > > select a.* from projects a inner join todos b on b.projectid = a.id > group by a.id order by b.duedate limit 10,20; And what does the plan look like? -- Gregory Stark EnterpriseDB http://www.e

Re: [GENERAL] Index trouble with 8.3b4

GIN problem kicks in with the "posting tree" representation. That would require more than about 85 records with the same matching lexeme-key. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's Pos

Re: [GENERAL] Index trouble with 8.3b4

656 of database 11511; blocked by process 7076. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Experiences with extensibility

es which Postgres doesn't have. Usually Postgres has more choices than any of the others but that doesn't mean that it includes the union of all of their feature sets. You'll probably get more useful answers if you ask a more specific question. -- Gregory Stark Enterprise

Re: [GENERAL] quick question abt pg_dump and restore

up with and picks the plan with the lowest cost. Scans which return many rows will tend to prefer bitmap index scans (when they don't do a full sequential scan of the t table) since it avoids random access. -- Gregory Stark EnterpriseDB http://www.e

Re: [GENERAL] tcp_keepalives_idle ignored

p keepalives are only going to help if you have clients disappearing -- such as networks being unplugged or client machines getting turned off. Under normal conditions when a client disconnects the postgres process will go away immediately. -- Gregory Stark EnterpriseDB http://www.e

Re: [GENERAL] A counter argument about DISTINCT and GROUP BY in PostgreSQL

<[EMAIL PROTECTED]> writes: > On Jan 4, 11:48 am, [EMAIL PROTECTED] (Gregory Stark) wrote: > >> And what does the plan look like? > > It looks great in mysql! Like what? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about Enterpri

Re: [GENERAL] Best practices for protect applications agains Sql injection.

quote your parameters before inserting them into the query but it's much more error-prone. It's also much harder to look at a piece of code and be sure it's correct. If you religiously use prepared queries then any variables interpolated directly into the query stand out like sore thu

Re: [GENERAL] REINDEX on large DB vs. DROP INDEX/CREATE INDEX

end of the transaction won't make a big difference. Checkpoints could be an issue, but you would be better off just raising checkpoint_segments and/or checkpoint_timeout to make sure you don't get one more often than once every few minutes when you're doing large operations li

Re: [GENERAL] check constraint question

start_time and/or end_time ranges in your where clause and the planner would prune partitions using both ranges. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -

Re: [GENERAL] A select DISTINCT query? - followup Q

S t > ON t.name = tbl.name > Or use the first() aggregate since you know there's only going to be one anyways: select name, first(comment) from tbl group by name having count(*) = 1 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com

Re: [GENERAL] handling of COUNT(record) vs IS NULL

# select count(ROW(null,null)=row(1,2)); count --- 0 (1 row) postgres=# select count(ROW(null,null)); count --- 1 (1 row) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] postgresql book - practical or something newer?

e last 1-2 years. When 8.3 comes out people will be looking for whole books on XML functionality, tsearch implementations, etc, and there will be nothing aside from the manual since they're all brand new features. -- Gregory Stark EnterpriseDB http://www.enterprise

Re: [GENERAL] expression index on date_trunc

to a timestamp without time zone for the time zone you specify). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] PostgreSQL Certification

moribund, which most people aren't even aware exist. I was going to suggest pruning the mailing lists down to just 3-4 already. The last thing we need to be doing is creating new ones. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me abou

Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification

I've never heard of them which tells you something about how heavily referenced they are. In any case search engine optimization is a mugs game. Concentrate on building a service that people want to use and people will talk about it and that will get you on the search engines. Searc

Re: [GENERAL] [OT] "advanced" database design (long)

ntly awkward for which you will end up with either EAV or 90 mostly NULL columns. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification

y the people you want but you should have to conduct yourselves out in the open where others have a chance to speak up and shout stop if you're doing something on their behalf that they don't like. [*] "New newsgroups are formed not on The Field Of Dreams theory- "if you

Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification

and talk about x without the others. People who don't want to talk about x are still interested in knowing that someone is talking about it and should still see that the discussion is happening even if they don't follow it in detail. -- Gregory Stark EnterpriseDB http://www

Re: [GENERAL] Question for Postgres 8.3

TF-8. C is a bit of special case since it sorts based on the binary representation rather than the characters. That's true for any 1-byte encoding based locale but C is more predictable when you actually have binary data. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com A

Re: [GENERAL] Subquery Factoring ?

emails where someone else was interested in it so that decision may be revisited for 8.4. And in any case there's still a good chance (though no guarantee) that recursive queries will be done for 8.4 which will definitely imply this feature. -- Gregory Stark EnterpriseDB http://w

Re: [GENERAL] Kernel kills postgres process - help need

ight say that keeping PHP processes around which are idle most of the time would be silly in itself. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--

Re: [GENERAL] 8.2/8.3 incompatibility

fo(buf, ") %s", rightop); - if (rightoptype != operform->oprright) - appendStringInfo(buf, "::%s", format_type_be(operform->oprright)); ReleaseSysCache(opertup); } -- Gregory Stark EnterpriseDB http://www.enterprisedb.com

Re: [GENERAL] SELECT CAST(123 AS char) -> 1

CHAR and then complaining about padding... That's what CHAR is for. If the other database doesn't support varchar it's so far from the SQL spec that writing something portable between it and something else is probably hopeless. -- Gregory Stark EnterpriseDB http://www

Re: [GENERAL] deadlock while re-indexing table

hink simple cases would be >> okay. What's that other process doing? >> > The other process is inserting into the user_profile table. Hm. This shouldn't be enough to cause a deadlock. Both inserts and reindex use the same method to get the list of indexes which returns them

Re: [GENERAL] deadlock while re-indexing table

e which requires an exclusive lock, then you'll be at risk of deadlocks when other transactions try to do the same thing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Re: [GENERAL] Why isn't an index being used when selecting a distinct value?

doesn't handle the two equivalent cases in exactly the same way and there are some plans available in one method that aren't in the other. That's only likely to help if you have relative few values of field1_id but it's worth trying. -- Gregory Stark EnterpriseDB

Re: [GENERAL] deadlock with truncate and foreing keys

aiting until any pending updates to t1 are committed and then blocking any subsequent updates before starting to rewrite t2. Assuming the truncates are rare compared to the updates this is probably the best combination. Alternatively putting a LOCK TABLE against t2 in transaction 2

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

timation in non-C locales (Tom) I seem to recall this largely had to do with negated regexp matches but I might only be remembering part of it. I would start by taking all the fixes for known bugs before trying to diagnose a new one :) -- Gregory Stark EnterpriseDB http://www.e

Re: [GENERAL] text and bytea

lt data type | Argument data types ++--+- pg_catalog | convert_to | bytea| text, name (1 row) Looks like they produce and consume byteas to me. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA servic

Re: [GENERAL] process pool

so the gain would be minimal. And in any case you can always add a pool outside of Postgres using things like pgpool or pgbouncer. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---

Re: [GENERAL] unnesesary sorting after Merge Full Join

e of the EquivalenceClasses to search for matching variables that were * equivalenced to constants. The interesting outer-join clauses were * accumulated for us by distribute_qual_to_rels. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's

Re: [GENERAL] autovacuum not freeing up unused space on 8.3.0

> > Process 2: > - running ANALYZE VERBOSE and pg_total_relation_size every second You'll probably have to vacuum pg_statistics as well then. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's P

Re: [GENERAL] Query meltdown: caching results

to invalidate cached objects whenever the underlying data changes. (Or you could even just use pl/php to update or invalidate the cached object through the same code library) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replicati

Re: [GENERAL] unnesesary sorting after Merge Full Join

"Tom Lane" <[EMAIL PROTECTED]> writes: > I wrote: >> Gregory Stark <[EMAIL PROTECTED]> writes: >>> as a path key, though we would have to create an equivalence class >>> and add COALESCE(id2,id1) to it as well I think. > >> No, because

Re: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)

> 4) How do you perform VACUUM? > > b) VACUUM - SQL command -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Merge Joins and Views

ajor planner surgery. In this case isn't all the planner needs the pathkey list to give it a hint that that ordering might be useful? It can't re-order the join but it can still try to produce those rows in any order which can be useful for the upper joins. -- Gregory Stark Enter

Re: [GENERAL] Merge Joins and Views

oo') from b ) as b using (a.y=b.y) The coalesce() doesn't depend on any join predicates, but if it's evaluated late it will be 'foo' for all the non-matching records instead of NULL. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about En

Re: [GENERAL] ANALYZE getting dead tuple count hopelessly wrong

em contradictory). However I'm skeptical about Simon's premise. It's not clear any changes to ANALYZE here are at the expense of other proceses. Any cycles saved in ANALYZE are available for those other processes after all... -- Gregory Stark EnterpriseDB http://www.ente

Re: [GENERAL] is it helpful for the optimiser/planner to add LIMIT 1

limit because without it the planner would have no idea that myfunction is only going to return 1 record. You could fix that more cleanly with "ALTER FUNCTION myfunction ROWS 1" but only if that's always true, not just for myfunction(3,5). -- Gregory Stark EnterpriseDB

Re: [GENERAL] is it helpful for the optimiser/planner to add LIMIT 1

"Ivan Sergio Borgonovo" <[EMAIL PROTECTED]> writes: > Summarising it up: is it worth to add it here and there as an > optimisation flag? Probably not. Unless you're not planning on reading all the resulting records anyways and want the planner to optimize with that

Re: [GENERAL] slow pgsql tables - need to vacuum?

sn't necessary then probably not. But there are usage patterns where it can still be necessary. If you load lots of sequential records then delete all but one for a time period, for example. That ends up leaving a page holding just one record which VACUUM can't clean up. -- Gregory

Re: [GENERAL] select distinct and index usage

d for DISTINCT, best. You might have to raise work_mem before the planner feels a hash will fit. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-general mailing list (pgsql-general@postgres

Re: [GENERAL] select distinct and index usage

t maybe > the first time. I think we would push a query through the planner to choose the best plan based on the statistics. I'm not sure how this would play with the visibility rules -- iirc not all scan types can be used with all visibility modes. And also I'm not sure how He

Re: [GENERAL] Problem after VACUUM ANALYZE

rds. That can cause strange things to happen if you truncate just some tables and run analyze on them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-general mailing li

Re: [GENERAL] SQL injection, php and queueing multiple statement

have a more complex policy where sometimes they're allowed if they come from internal data structures or they've been checked then I think it would be helpful. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -

Re: [GENERAL] Postgres on shared network drive

of a days/weeks old database, there are chaper and better ways of >> doing it. Actually that's a great way of doing it. I think we're going to run into this "problem" more and more often, that there are good lower-level solutions to the same problems we're tackling

Re: [GENERAL] Unacceptable postgres performance vs. Microsoft sqlserver

-- Aggregate (cost=5088.00..5088.01 rows=1 width=0) -> HashAggregate (cost=2838.00..3838.00 rows=10 width=4) -> Seq Scan on accounts (cost=0.00..2588.00 rows=10 width=4) (3 rows) -- Gregory Stark

Re: [GENERAL] Bitmap Heap Scan takes a lot of time

h will actually run the query and include information to compare against the estimates. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] plpgsql functions and the planner

nction. Before 8.3 it still wouldn't get replanned until you started a new session but 8.3 is more clever. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-general mailing list (pgsql-general@postgre

Re: [GENERAL] Sorting nulls and empty strings together

g an index at all for the order by. You'll either be searching on other columns which would have to be leading columns of every index or you'll be reading the whole table anyways and postgres will prefer to sort since it's faster. -- Gregory Stark EnterpriseDB http://www.

Re: [GENERAL] inheritance. more.

fast but a partitioned table can do a sequential scan of a single partition. c) Makes loading pre-organized segments of data and dropping segments O(1) which is makes the data much more manageable. It's really (c) which is the killer app for partitioned tables. (a) and (b) are

Re: [GENERAL] psql \pset pager

is all the time with \set AUTOCOMMIT. >> I didn't find this documented. Is the acceptance of bad input by design >> or an oversight? > > Will be fixed in 8.4. I expect there'll be some screams from people with scripts which start failing. I wonder whether

Re: [GENERAL] Error: Could not open relation...

led back. I would be more concerned about integrity issues if you started having the same error with something like an xlog WAL file though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-gener

Re: [GENERAL] Bottom Posting

hat there's really no reason to quote the original message. My response was only to this one point and not the longer previous point. I actually think this is a more important point to get across than simply "don't top post" which just seems to generate lots of "botto

Re: [GENERAL] Open Source CRM - Options?

referring to is some subset of CMS, I don't know of any specific name for this kind of system. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-general mailing list (pgsql-general@post

Re: [GENERAL] active queries

n or all of libpq. You can actually have more than one query simultaneously active in the same connection using named portals. But if you called PQgetResult() on one you have to ensure all threads wait until it returns before issuing PQgetResult() (or any other libpq function) on the other por

Re: [GENERAL] Psql crashes with Segmentation fault on copy from

gt; P&C PREMIUM\t6300\t0\t183718"..., > scan_state=0x549c20, query_buf=0x54a480) at command.c:546 I'm a bit puzzled by this. The command being executed here seems to have been overwritten by data from the later command -- before it was overwritten it was apparently "\i b

Re: [GENERAL] Bottom Posting

"Bob Pawley" <[EMAIL PROTECTED]> writes: > The concept of most lists should be "the free exchange of ideas in the most > efficient manner possible". What is this in response to? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask m

Re: [GENERAL] small table, huge table, and a join = slow and tough query. cake inside!

you're talking about they probably won't help unless you have a *lot* of different pollutants and not all that many records for each pollutant. To get anything more you would have to post an EXPLAIN output and preferably an EXPLAIN ANALYZE output if you can find a query which completes. -

Re: [GENERAL] turning fsync off for WAL

and checkpoint_segments parameters. And probably the bgwriter variables as well (lest it start trying to flush buffers whose changes haven't been logged yet too). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseD

Re: [GENERAL] turning fsync off for WAL

as well turn fsync off on the server since you're resigned to having to restore from backup on a server crash anyways... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Heavily fragmented table and index data in 8.0.3

time as a very long-running transaction such as pg_dump it shouldn't be a problem. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] when to reindex?

t different records. The cases where it would win would be where you have a lot of dead space in the table (but not in the index), partial indexes which don't cover much of the table, or a table which is already very well clustered (possibly, depending on other factors). --

Re: [GENERAL] Memory use in 8.3 plpgsql with heavy use of xpath()

en. That's just a special case of what would be expected to happen with memory allocation anyways though. Few allocators return memory to the OS anyways. It might just be exaggerated in this case since probably a significant part of Postgres's footprint here was the per-transaction

Re: [GENERAL] Functional index adding one

d version of Postgres, I think 7.3 which had functional indexes but not expression indexes. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.o

Re: [GENERAL] Functional index adding one

"Gregory Stark" <[EMAIL PROTECTED]> writes: > CREATE INDEX token_position_func on (token(position+1)) Ooops, I misread that as if "token" were a function and not the table. Sam Mason had the right syntax. Sorry. -- Gregory Stark EnterpriseDB http://

Re: [GENERAL] Switching between terminals

be need to put a SELECT pg_sleep(10) between the two queries in the first file you run so that it hasn't updated both tables and exited before the second one even starts. But I'm just guessing since you haven't sent the actual files you're running. -- Gregor

Re: [GENERAL] AT TIME ZONE and DST in UTC<->CET conversion

GMT+1. If you want a timezone which switches between CET and CST automatically you should use something like Europe/Paris. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pg

Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error

re --enable-debug CFLAGS=-O0 CFLAGS=-O0 ./configure --enable-debug And yes, you have to do make clean. I often forget that step :( -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent

Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error

"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: >> "Tom Lane" <[EMAIL PROTECTED]> writes: >>> (Rather than trying to browbeat configure into doing this, I'd suggest >>> manually adjusting CFLA

Re: [GENERAL] foreign key restrictions

ate values of OFONE but they'll all have different values of ID anyways. I'm not sure if there's a fundamental reason why there has to be an index that exactly matches the foreign key or not -- offhand I can't think of one. -- Gregory Stark EnterpriseDB

Re: [GENERAL] Postgres 8.3 is not using indexes

"Clemens Schwaighofer" <[EMAIL PROTECTED]> writes: > Any tips why this is so? They don't appear to contain the same data. If they do have you run analyze recently? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB'

Re: [GENERAL] indexes on functions and create or replace function

t using the index during the unavoidable window when the index is invalid. I'm not sure how to solve that. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgs

Re: [GENERAL] MERGE: performance advices

keep only the first value for each pk. Then you'll have to build indexes, swap the tables, and fix any views or rules which refer to the old table (they'll still refer to the old table, not the new table even after renaming it to the old name). -- Gregory Stark EnterpriseDB

Re: [GENERAL] recover in single-user backend fails

eturn;" as the first line of gist_redo() in gistxlog.c and when recovery (hopefully) completes immediately drop any gist indexes? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-gener

Re: [GENERAL] A challenge for the SQL gurus out there...

n every thread (and possibly a recent_post_id) then you could have a query on forums which pulls the most recently updated thread directly without having to join on form_post at all. That would slow down inserts but speed up views -- possibly a good trade-off for a forum system. --

Re: [GENERAL] "Healing" a table after massive updates

do ALTER TABLE tab ALTER col TYPE USING col; which will rewrite the table without using an index. This is much faster but has the same other disadvantages. Incidentally the visibility bugs are indeed entirely fixed in 8.3. In 8.2 and before cluster and alter table rewrites can both cause tupl

Re: [GENERAL] "Healing" a table after massive updates

Tom Lane <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: >> Incidentally the visibility bugs are indeed entirely fixed in 8.3. In 8.2 and >> before cluster and alter table rewrites can both cause tuples to not appear >> for transacti

Re: [GENERAL] Index order

th any accented characters or in latin1 since they'll be sorted by their binary value which comes after all the unaccented characters. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-general m

Re: [GENERAL] Static functions

esult is >> > already known? >> >> Not if said effort would cost more than is saved, which would be by far >> the most likely result if we tried to cache all function results. >> > > Sorry Tom, I confused STABLE with IMMUTABLE; my bad. No, this is equally

Re: [GENERAL] PQexecParams question

"Grzegorz Jaśkiewicz" <[EMAIL PROTECTED]> writes: > that would be a type mismatch, heh. prepare "select * from foo where a = any($1::int[])" then pass "{1,2,3}" -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruc

Re: [GENERAL] Chart of Accounts

at $1.999/gallon and buy 4 gallons you get charged $8.00 not $7.996. If you fail to round at that point you'll find that your totals don't agree with the amount of money in your actual bank account. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about Enterpri

Re: [GENERAL] Out of memory in create index

other services or queries running at the same time the first time which reduced the available memory. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-general mailing l

Re: [GENERAL] Drupal and PostgreSQL - performance issues?

om earlier messages. Many mailers hide such comments or de-emphasize them to help the user concentrate on the new material. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] Drupal and PostgreSQL - performance issues?

y. ISTM the right way to make it go away is to allocate temporary files and mmap them instead of using sysv shared memory. Then we can mmap as much as we want. Before we lose root privileges we can even mlock as much as we want. -- Gregory Stark EnterpriseDB http://www.enterprised

Re: [GENERAL] PostgreSQL 8.3.3 chooses wrong query plan when LIMIT 1 added?

ps increasing the statistics target. One thing that comes to mind though, I would have defined one of those two indexes to include both columns. Probably the file_id index, so you would have an index on and an index on . That would be a huge win for this query. -- Gregory Stark EnterpriseDB

Re: [GENERAL] psql screen size

r may be different). Perhaps we need to tell readline whenever we run a subprocess and it may have missed screen resize signals. It's easy enough to work around, just resize the window again a little bit once you're at the prompt. Readline notices that and adjusts. -- Gregory Stark

Re: [GENERAL] psql screen size

Gregory Stark <[EMAIL PROTECTED]> writes: > Could you define "messed up"? > > What I see is that the query output is formatted correctly but readline still > thinks the screen is the old size. (This is in CVS HEAD -- this code was > definitely different in 8.3 and

Re: [GENERAL] Decreasing WAL size effects

the archive command the amount of real data in the wal file and have it only bother copying up to that point? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-general mailing list (pgsql-general

<    1   2   3   4   >