f Postgres has used up all
its existing WAL files it will have to create new ones which does have some
performance cost. But otherwise it's unaffected.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
ion failures frequently?
There's no reason for an individual transaction to take longer in SERIALIZABLE
mode. In fact I believe SERIALIZABLE mode is actually measurably faster in
benchmarks but haven't run one in READ COMMITTED mode recently (for that
reason).
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
alternative.
> I think I went with bsearch mainly because I knew I could generate
> the TID list already sorted. Somebody should try to measure the
> probe time of the tidbitmap code against bsearch ...
Incidentally, there was a previous discussion about this a while back.
I can't see
StringLength as sizeof() ought to give a
compile-time error.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Index Cond: ((code)::text = 'NRN15'::text)
So you might want to increase the statistics target for the "code" column.
Incidentally the way this is written makes me wonder what data type "code" is
defined as.
--
Gregory Stark
EnterpriseDB
"Ow Mun Heng" <[EMAIL PROTECTED]> writes:
> On Mon, 2007-09-03 at 11:31 +0100, Gregory Stark wrote:
>> "Ow Mun Heng" <[EMAIL PROTECTED]> writes:
>> >
>> > How can I persuade PG to use the index w/o resorting to setting seqscan
>> &
t the explain analyze
with and without enable_seqscan now that the stats are giving good
predictions?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ed just for the one
database. There's not any standard definition of what indexes you'll need for
all databases out there. Indexes aren't even in the SQL standard because
they're part of performance tuning for each individual database engine.
--
Gregory Stark
EnterpriseD
thing is a general question.
Yes.
You can even do this with GROUP BY as long as the leading columns of the ORDER
BY inside the subquery exactly matches the GROUP BY columns.
In theory we can't promise anything about future versions of Postgres but
there are lots of people doing this already s
1627 (number of loops)? But even then I get 396407.161 ms,
> which is still far away from the 532673.631 ms in the parent node.
The nested loop still has to do some work. Actually it's quite possible that
that extra overhead in nested loop is largely gettimeofday() calls fo
"Chris Browne" <[EMAIL PROTECTED]> writes:
> [EMAIL PROTECTED] (Gregory Stark) writes:
>
>> You can even do this with GROUP BY as long as the leading columns of
>> the ORDER BY inside the subquery exactly matches the GROUP BY
>> columns.
...
> Is there
e, selection_type). But your join condition is matching
on (search_id, user_id, ***selected_value***, selection_type).
I'm not sure what these columns are but this looks like a bug. Certainly it
will make PostgreSQL less likely to use the index since it can only use the
first two columns of it
;s not much you can do with en_US or ru_RU.
I think most big commercial sites that decide they need this just buy access
to one of the ip to geographic location services which are far from perfect
but in my experience are plenty good enough to get a reasonable time zone.
a diff in that they tell you when
the change occurred, what user made the change, and if multiple changes to the
same record occurred you get a record of each.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)--
o access them (including
autovacuum) then nothing will notice they're missing. But if you do try to
access them you'll get an error. And if you leave it in this situation too
long your database will shut down from getting too close to transaction
wraparound.
--
Gregory Stark
h case you might want to avoid
sequential scans if index scans would avoid accessing some segments.)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please sen
"Gustav Lindenberg" <[EMAIL PROTECTED]> writes:
> select * from security.users where length(us_username)=0;
> Surely this a null.
Surely not.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)-
oing to want the megaraid driver which is in the stock
kernel tree but may or may not be compiled in your binary kernel distribution
packages.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 9: In v
If you're writing data to the
raid in large contiguous chunks then it you get higher bandwidth than RAID1+0.
The problem with RAID5 is that if you're writing random access chunks then
it's even slower than not having a raid at all.
--
Gregory Stark
Enterpris
s of these queries say?
select count(*),count(distinct suid) from mt;
select suid,count(*) from mt group by suid having count(*) > 1;
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
but in the
long term I think it would be better to know exactly what happened here.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
he index pages will
have those few values left on them preventing them from being reused.
If that's not your usage pattern then perhaps you should describe your usage
pattern in more detail. But I suspect you would be best served by simply
vacuuming much more often.
--
Gregory Sta
o just thought of another scenario, I could've created a temp
> table from a SELECT with the correct column order - wish I had
> thought of that before writing this email.
When you think of it put it in an email :)
--
Gregory Stark
EnterpriseDB http:/
ot; cycle in
8.2?
I also wonder if it doesn't make more sense in 8.2 if your goal is to avoid
drop-outs to just give up on the lru cycle entirely and set the delay to
something like 60s and the all_percent to 100. Effectively saying to flush all
dirty buffers once a minute to smooth th
lso write it using a subquery instead of a join
SELECT *
FROM (
SELECT dom_id, dom_name,
(SELECT count(*) FROM users WHERE user_dom_id = dom_id) as nusers
FROM domains
) as subq
WHERE nusers > 0
ORDER BY dom_name
But that will perform worse in many cases.
--
way you describe. It denormalizes the data for very fast but less flexible
operations.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
"Jeff Davis" <[EMAIL PROTECTED]> writes:
> On Wed, 2007-09-26 at 16:54 +0100, Gregory Stark wrote:
>
>> You could check out the tablefunc contrib which includes a function called
>> connectby() which implements a kind of recursive query.
>>
>> Alt
Because of MVCC: 'select count(*)' without
>> WHERE-condition forces an seq. table-scan.
>
> But he does have a WHERE condition. THe problem is, probably, that the
> condition is not selective enough so the planner chooses to do a
> seqscan.
What does EXPLAIN SEL
ring to the column "r", it would look like
SELECT (r).a from (select row(tab1.*)::tab1 as r from tab1)x;
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
ld this have something to do with the lack of speed?
That sounds about right. It's sampling 10x as much of the table and it's
taking almost 10x as long...
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
I'm no expert on locking in Postgres, but AFAIK locks that prevent you
> from reading records are rather rare and probably only issued from
> userland code.
Pages can be locked but never for very long.
What other work is going on in this server? Is there anything which might be
locking t
)
>
> Still stymied about the seemingly random performance, especially since I
> have seen this query execute in 2 minutes.
And the "explain analyze" for these?
Are you still sure it's certain date ranges which are consistently problems
and ot
ecords being randomly accessed.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ve made sense to have the driver do
the variable substitution and execute the query without parameters. Also, if
in the future we do handle multiple prepared plans for prepared statements it
would run into this kind of problem.
But then even in that case your solution still works. I'm just menti
e talking about order of 5 minutes downtime in exchange you
avoid the risk of several data loss, data corruption, server cash, and
security holes.
Actually there are a few changes in 7.4.x that could require more work to
upgrade. If your database locale is something like Hungarian or if you have
enerally it's just how Internet mailing lists work.
There's some header you can add to your own emails to request mailers not add
it. But I don't remember what it is.
You can also tell majordomo not to forward you messages on the list to which
you were cc'd.
--
tunately the fancy SQL feature you're looking for is ROLLUP which
Postgres doesn't support.
I think you would have to do
(
select id, code, amount from foo where code < 10
union all
select null, code, sum(amount) from foo where code < 10 group by code
) order by code, id
--
ct the first or second half of the columns but put two
copies of them in the column list.
Are the client and server on two separate machines? Is it possible you have a
network issue between these two machines (like pmtud problems, for example)?
--
Gregory Stark
EnterpriseDB
s,
don't refer to it directly. And unless you mark it with storage plain always
detoast it before working with an argument or anything from heap_deform_tuple.
In postgres we normally put pg_detoast_datum() directly into the DatumGetFoo()
and PG_GETARG_FOO_P() macros.
--
Gr
"Ron Peterson" <[EMAIL PROTECTED]> writes:
> 2007-10-13_08:50:56-0400 Ron Peterson <[EMAIL PROTECTED]>:
>> 2007-10-13_01:22:06-0400 Gregory Stark <[EMAIL PROTECTED]>:
>
>> > And normally you would define your own datatype and not use bytea.
>&
"Ron Peterson" <[EMAIL PROTECTED]> writes:
> I think I can get where I want to go without completely figuring that out
> right now though...
What are you trying to do?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
--
using heap_form_tuple. And using a composite type is
probably the right approach. Other utilities like pageinspect do use composite
types for things like this.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
ou probably don't want to name your type starting with an _.
Postgres names array types starting with _ so that's likely to confuse
something and if not something then at least someone.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end
t with
set date_field = coalesce(date_field, some_date)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
e concerned with cpu usage though.
It also requires extra indexes if you want to be able to use an index for
LIKE. And you can't do substring() or length() on a very large datum without
fetching the whole datum.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.co
) is OK
> syntax but not UNIQUE(my_function(x)).
Really? It doesn't work for me in the ADD CONSTRAINT syntax. I don't think you
can use the ADD CONSTRAINT syntax, you have to use the CREATE UNIQUE INDEX
syntax. It's effectively the same in Postgres anyways.
--
Gr
as an error if you're running thousands of queries per
minute. The logs fill up and even if you filter the logs it imposes extra
run-time overhead. You end up having to avoid the warning just as if it had
been an error.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
-
ou're selecting on being stored out of line ("toasted") which
would hurt performance if you're often accessing many of those columns.
If it's not true then you may have a lot of dead space in your table which
would decrease performance.
--
Gregory Stark
eir Adsense or other more
normal use data structures in anything but a bog-standard SQL database.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
used in English such as Greek characters. Is this Windows or some
form of Unix?
Do you have a particular LIKE pattern and a particular example for which it
fails? Or does it work reasonably when you test individual values but fail
when you perform large searches?
--
Gregory Stark
Enterpris
e the problem
> here, without success. Now I was using 8.1.10 on Linux (I gather your
> platform is not Linux from the spelling of the locale names)
Really? On my Debian system the locales are named precisely like that.
What do they look like on Red Hat?
--
Greg
d objects such as GUI elements. These are allocated
in shared space so they can be manipulated by any process running in that
"desktop".
Why Shell32 and User32 are allocating space in there just to initialize
themselves or handle these basic utility functions is a bit o
will lock the
referenced keys. If they lock the referenced keys in different orders then
it's possible for them to deaadlock. This is addressed in more recent versions
of PostgreSQL.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broad
chr, as we do
. Make "role is not permitted to log in" errors not be hidden
. Remove quotes around locale names in some places for consistency.
. Add missing entry for PG_WIN1250 encoding, per gripe from Pavel Stehule.
Also enable translation of PG_WIN874
--
Gregory Stark
Enter
p of Postgres's current memory allocations and could be useful in
showing if there's a memory leak causing this.
Also, what version of Postgres is this?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)
t of date stats and re-post both plans.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
"Gregory Stark" <[EMAIL PROTECTED]> writes:
> "Reg Me Please" <[EMAIL PROTECTED]> writes:
>
>>-> Seq Scan on tt_elem (cost=0.00..29.40 rows=1940 width=8)
>> (actual time=0.012..0.0
ind to tune other things properly.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
s which play with the locale or a locale like
hungarian which compares some different strings as equal then you might have
to reindex.
Otherwise it's just a Postgres server restart's worth of downtime. There are
both data eating bug fixes and security fixes in 8.1.10 for yo
rather than the other way
around. So you just need to track those sql files in your revision control
system, and they're just plain text.
The situation is complicated somewhat by the SQL "ALTER TABLE" and so on
commands which you need to use instead of just reissuing the CR
granularity. Perhaps one partition per day instead of one per
30s. you could drop a partition when all the keys in it are marked as dead.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
? How do
>> I need to structure the table, and what query do I have to run in
>> order to make it happen?
>
> You need to look at the connectby function which is part of contrib.
Or ltree. Depending on how static your data is and what else you need to do
with it.
--
Grego
"Sam Mason" <[EMAIL PROTECTED]> writes:
> On Fri, Oct 26, 2007 at 08:25:09AM +0200, Thomas Finneid wrote:
>> Gregory Stark wrote:
>> >Tom's point is that if you have 55k tables then just *finding* the newest
>> >child table is fairly expensive. You&
, I
> guess no one really cares about consistency or traditions.
You can get the traditional behaviour out of glibc if you set POSIXLY_CORRECT.
One thing to beware of is that patch behaves very strangely with
POSIXLY_CORRECT set though.
--
Gregory Stark
EnterpriseDB
r by
nextval('s');
nextval
-
11
12
13
14
15
16
17
18
19
20
(10 rows)
That's certainly not how I remembered it working but I'm not sure I ever
tested it before.
--
Gregory Stark
EnterpriseDB http:/
this substitution kind of makes sense if
you're thinking about things the way the spec does. It doesn't make much sense
if you're thinking the way Postgres does of having arbitrary expressions there
independent of what's in the select list.
--
Gregory Stark
Enterprise
ec and
> postgres takes 1 m10 sec . Is there anything that can be done in postgresql
> for speeding this up?
How large are the actual respective data files?
What are the columns in these tables? Do you have many char() and NUMERIC
columns?
--
Gregory Stark
EnterpriseDB http:
views and make explicitly constructed scans
in the backend use NULLS LAST.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ost
> recent price for a given stock for each week (or month or quarter or year).
Do you care what happens if there were no trades for a given stock in the time
period? The query you give above using MAX would still work but the query I
described using DISTINCT ON would not emit a record for t
e records is updated
while the delete is running.)
You should note this will delete 50,000 arbitrary records. Not necessarily the
50,000 oldest ones or anything useful.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication suppor
ll want to modify your function to include an ORDER BY
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL
training!
---(end of broadcast)---
TIP 9: In versions belo
able day when it turns out the 24 fields
aren't unique and you need to consider adding a 25th column to the table *and
every table referencing it* as well as changing every line of application code
to use the new column.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ge
ecords it
will magically find a record that says it should in fact have skipped 50
records. Of course then if the next record says it should only have skipped 1
record then what does that mean?
The limit and offset values can be anything, even something "VOLATILE" like
random() or a subquery, b
"Abraham, Danny" <[EMAIL PROTECTED]> writes:
> Is there a way to break the PGresult array to chunks
> Like Oracle?
There isn't a protocol-level way but there is a way in SQL, use cursors and
"FETCH FORWARD "
--
Gregory Stark
EnterpriseDB http:
a query like
SELECT * FROM (SELECT * FROM (query) LIMIT n) OFFSET n
or vice versa.
Or alternatively do the arithmetic. If there's already an offset in the query
structure when Skip() is called then add that amount to the offset. I'm
assuming your methods are called on some kind of ob
being
able to add and drop entire partitions effectively instantaneously.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
---(end of broadcast)---
TIP 1: if posting/reading through U
eans... Mach... which is entirely
> different than say FreeBSD at the kernel level.
I think (but I'm not sure) that the kernel in OSX comes from BSD. What they
took from NeXT was the GUI design and object oriented application framework
stuff. Basically all the stuff that Unix programmers s
you're writing a driver implementing the protocol from scratch you could
expose chunks of results to the application but there's no protocol-level
support for it so you can't directly control the rate at which results arrive
or the chunk size or anything like that.
--
Gregory
not it's only a matter of time before they do something bad to your
data.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
---(end of broadcast)---
TIP 1: if pos
encoding you'll be stuck. Postgres doesn't support using multiple encodings in
the same database (or effectively even in the same initdb cluster).
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres sup
;62'
AND person_id IN (
SELECT person_id
FROM person
WHERE column1=1
AND column2='189'
)
or
SELECT person_id
FROM person AS parent
WHERE column1=1
AND column2='62'
AND EXISTS (
SE
here to whatever that method returns there for some record
somewhere... And it's awfully hard to index and join between complex
expressions picking data out from inside objects on both sides, etc.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB&#
nd of bitmap scan
with a bitmap of ids. And once the bitmap is done scan an index on person for
just the matching records. Postgres doesn't support anything like this (yet:).
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS
"rihad" <[EMAIL PROTECTED]> writes:
> Hi, would full_page_writes=off increase the risk of inconsistency or db
> corruption in 8.3 and FreeBSD 7?
yes.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB&#
a SELECT against the referencing column.
It does
> That is, in this
> example, if the following effectively happens:
>
> SELECT * FROM B WHERE O = 1;
Actually the query is (effectively, assuming your equality operators are named
"=" and the columns match in type)
SEL
ecute 4 billion statements even if it's in a loop.
8.3 helps this by making most read-only operations not count.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
---(end of broadcast)--
twice in the
same query like "select avg_vel(position, pos_time), avg_vel(position2,
pos_time2) from ..."
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
---(end of broadcast)-
You're more likely to see responses if you
post in a new thread.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL
training!
---(end of broadcast)-
ck_size
----
8192
(1 row)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
'0001'
> select pg_column_size('a'::text), pg_column_size(1::numeric),
> pg_column_size(3111234::numeric);
> pg_column_size | pg_column_size | pg_column_size
> ++
> 5 |
bute and in the where clause (either directly or the computation) or
> b.) precomputing the bin and directly accessing the child table will be the
> only options we have for now.
Or the near future.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
only having said that then say *how* to quote it.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
h those
> provisions. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the sender
> by
> reply e-mail and destroy all copies of the original message.
FWIW this would be another item on the netiquette FAQ
ntences of your message? If you did would you have any trouble
finding the original message to reread it?
Top-posting makes perfect sense if you start from the broken place of assuming
you need to copy the entire thread into every message. It's a bit like saying
"but officer I had to spe
ople
> with hammers over this minor infraction. It really makes one feel
> unwelcome.
I'm sorry, to what were you referring? To which Greg were you responding to?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres sup
e worst case it has to do nearly as much work as the original
database did. And it only gets to use 1 cpu so it can only have one i/o
request pending.
bgwriter is started already when doing recovery, right? Perhaps things could
be helped by telling bgwriter to behave differently during recov
; (Larry Wall, Apocalypse 6)
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
I agree.
--
Gregory Stark
EnterpriseDB http://www.enterprise
, what version of the server are you using?
Have you updated to the latest bug-fix release for that version?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
I wouldn't be complaining.
Depending on your transaction mix and what percentage of it is read-only
select queries you might reasonably expect the restore to take as long as it
took to generate them...
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> On Wed, 12 Dec 2007 18:02:39 +0000
> Gregory Stark <[EMAIL PROTECTED]> wrote:
>
>> I'm not sure what you guys' expectations are, but if you're restoring
>> 5 minutes worth of database traffic
101 - 200 of 385 matches
Mail list logo
