> If the DBA notices that there is a problem with a query, he adds
> an index, he
> notices that there is no difference, then he notices that
> PostgreSQL is not
> using his index. First and foremost he gets mad at PostgreSQL for
> not using his
> index. If PostgreSQL decided to use an index which
Oliver Elphick wrote:
>
> On Wed, 2002-04-17 at 06:51, mlw wrote:
> > I just think there is sufficient evidence to suggest that if a DBA creates an
> > index, there is strong evidence (better than statistics) that the index need be
> > used. In the event that an index exists, there is a strong in
On Wed, 2002-04-17 at 06:51, mlw wrote:
> I just think there is sufficient evidence to suggest that if a DBA creates an
> index, there is strong evidence (better than statistics) that the index need be
> used. In the event that an index exists, there is a strong indication that,
> without overwhel
On Wed, 2002-04-17 at 06:51, mlw wrote:
> I just think there is sufficient evidence to suggest that if a DBA creates an
> index, there is strong evidence (better than statistics) that the index need be
> used. In the event that an index exists, there is a strong indication that,
> without overwhel
Christopher Kings-Lynne wrote:
>
> > I have bitched about the index stuff for a while, and always have
> > bumped up
> > against this problem. If I can sway anyone's opinion, I would say, unless
> > (using Tom's words) a "factor of 2" planner difference against, I
> > would use an
> > index. Rath
Tom Lane wrote:
>
> mlw <[EMAIL PROTECTED]> writes:
> > That is the difference, in another post Tom said he could not get
> > excited about 10.9 second execution time over a 7.96 execution
> > time. Damn!!! I would. That is wrong.
>
> Sure. Show us how to make the planner's estimates 2x more ac
On Tuesday 16 April 2002 10:32 pm, you wrote:
> On Tuesday 16 April 2002 05:48 pm, you wrote:
> > I want to change the date from a field in a tuple in a trigger_function
> >
> > create table example (
> > my_date datetime
> > ...
> > );
> >
> > int na;
> > char select[20];
> >
> > na = SPI
> I have bitched about the index stuff for a while, and always have
> bumped up
> against this problem. If I can sway anyone's opinion, I would say, unless
> (using Tom's words) a "factor of 2" planner difference against, I
> would use an
> index. Rather than needing clear evidence to use an index
mlw <[EMAIL PROTECTED]> writes:
> That is the difference, in another post Tom said he could not get
> excited about 10.9 second execution time over a 7.96 execution
> time. Damn!!! I would. That is wrong.
Sure. Show us how to make the planner's estimates 2x more accurate
(on average) than they a
Bruce Momjian wrote:
>
> Let me add people's expections of the optimizer and the "it isn't using
> the index" questions are getting very old. I have beefed up the FAQ
> item on this a month ago, but that hasn't reduced the number of
> questions. I almost want to require people to read a specifi
I have updated the developer's FAQ with this information:
---
1.12) Where can I get a copy of the SQL standards?
There are two pertinent standards, SQL92 and SQL99. These standards are
endorsed by ANSI and ISO. A draft of
Let me add people's expections of the optimizer and the "it isn't using
the index" questions are getting very old. I have beefed up the FAQ
item on this a month ago, but that hasn't reduced the number of
questions. I almost want to require people to read a specific FAQ item
4.8 before we will r
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Top ten calls:
> % cumulative self self total
> time seconds secondscalls ms/call ms/call name
> 36.95 9.87 9.87 74882482 0.00 0.00 pq_getbyte
> 22.80 15.96 6.09 11 553.64 1450.9
I have just added the following developer's FAQ item. The SQL99 URL in
the attached email, ftp://jerry.ece.umassd.edu/isowg3/x3h2/Standards/,
is not longer active so I did not mention it.
---
1.12) Where can I get a copy
Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> While trying to optimise a query I found that running VACUUM ANALYSE
> changed all the Index Scans to Seq Scans and that the only way to revert
> to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.
>>
>> EXPLAIN ANALYZE output w
Michael Loftis <[EMAIL PROTECTED]> writes:
> Realistically the system should choos *ANY* index over a sequential
> table scan.
Sorry, I do not accept that. You might as well say that we should
rip out any attempt at cost estimation, and instead put in two or
three lines of brain-dead heuristics
Michael Loftis <[EMAIL PROTECTED]> writes:
> Reading all of this discussion lately about how the planner seems to
> prefer seqscan's in alot of places where indexes would be better starts
> making me wonder if some of the assumptions or cals made to figure costs
> are wrong...
Could well be.
Understood. It's not what I was expecting to happen.
Normally I always specifically state the match, so I was a little
surprised by the behaviour.
Makes sense to match the primary key and only the primary key though.
--
Rod Taylor
Your eyes are weary from staring at the CRT. You feel sleepy.
> On Tue, 2002-04-16 at 03:20, Tatsuo Ishii wrote:
> > In my understanding, our consensus was enabling multibyte support by
> > default for 7.3. Any objection?
>
> Is there currently some agreed plan for introducing standard
> NCHAR/NVARCHAR types.
I have such a kind of *personal* plan, maybe fo
On Tue, 16 Apr 2002, Michael Loftis wrote:
> I know I know, replying to myself is bad and probably means I'm going
> insane but thought of one other thing...
>
> Realistically the system should choos *ANY* index over a sequential
> table scan. Above a fairly low number of records any indexed que
I know I know, replying to myself is bad and probably means I'm going
insane but thought of one other thing...
Realistically the system should choos *ANY* index over a sequential
table scan. Above a fairly low number of records any indexed query
should be much faster than a seqscan. Am I rig
Reading all of this discussion lately about how the planner seems to
prefer seqscan's in alot of places where indexes would be better starts
making me wonder if some of the assumptions or cals made to figure costs
are wrong...
Anyone have any ideas?
Louis-David Mitterrand wrote:
>On Tue, Ap
En Tue, 16 Apr 2002 09:41:19 -0300 (ADT)
"Marc G. Fournier" <[EMAIL PROTECTED]> escribió:
> Could some ppl test out archives.postgresql.org and let me know if they
> notice any differences in speed?
Well, it's impressive.
One thing I don't like about archives.postgresql.org is that when it
show
En Tue, 16 Apr 2002 19:21:50 -0400 (EDT)
Bruce Momjian <[EMAIL PROTECTED]> escribió:
> Here is a patch based on this discussion.
I still think the xdigit class could be handled the same way the digit
class is (by enumeration rather than using the isxdigit function). That
saves you a cicle, and I
Gavin Sherry wrote:
> > I see what it is doing, but it seems quite unclear. Seeing that people
> > are using this as a pattern for other param processing, I will work on a
> > patch to convert this to DefElem.
>
> Wouldn't a few macros clean this up better (ie, make it clearer)?
>
> #define CDB
On Tue, 16 Apr 2002, Bruce Momjian wrote:
> The code that bothered me about the CREATE DATABASE param processing
> was:
>
> /* process additional options */
> foreach(l, $5)
> {
> List *optitem = (List *) lfirst(l);
>
> switch (lfirsti(optitem))
> {
>
Tom Lane wrote:
> Oh, I think we were talking at cross-purposes then. What you're really
> unhappy about is that this uses a list of two-element sublists? Yeah,
> I agree, that's a messy data structure; a list of DefElem would be
> perhaps cleaner. Not sure if it matters all that much though, s
Bruce Momjian <[EMAIL PROTECTED]> writes:
> The code that bothered me about the CREATE DATABASE param processing
> was:
> /* process additional options */
> foreach(l, $5)
> {
> List *optitem = (List *) lfirst(l);
> switch (lfirsti(optitem))
> {
> ca
Manuel Sugawara wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> >
> > Basically, you manually preprocess the patch to include the
> > USE_LOCALE branch and remove the not USE_LOCALE branch.
>
> Yeah, that should work. You may also remove include/regex/cclass.h
> since it will not be used
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Tue, 16 Apr 2002, Rod Taylor wrote:
>> You'll notice there isn't a primary key at all -- which shouldn't be
>> an issue as there is still the unique.
> If you're not specifying the columns in the references constraint, it
> means specifically referen
While it is true that you can't do binary searches on compressed indexes you
may get a large payoff with compressed indexes since the index fits in fewer
pages and so may be more efficiently cached in the buffer pool. Even a
small reduction in io load may compensate for the higher computational
d
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 3:58 PM
To: Peter Eisentraut
Cc: Fernando Nasser; [EMAIL PROTECTED]
Subject: Re: [HACKERS] Operators and schemas
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> I can hardly believe that we want to
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> I can hardly believe that we want to implement this just to be able to
> check off a few boxes on the SQL-compliance test. Once you have the
> ability to use a fixed list of statements in this context it should be
> easy to allow a more or less arbit
-Original Message-
From: Peter Eisentraut [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 3:33 PM
To: Fernando Nasser
Cc: Tom Lane; [EMAIL PROTECTED]
Subject: Re: [HACKERS] Operators and schemas
Fernando Nasser writes:
> I agree. And for Entry level SQL'92 we are done -- only
Fernando Nasser writes:
> I agree. And for Entry level SQL'92 we are done -- only tables, views
> and grants are required. The multiple schemas per user is already
> an intermediate SQL feature -- for intermediate SQL'92 we would still
> need domains and a character set specification.
>
> For
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes:
> Wow, I am completely at a loss why you would not allow implicit coercions
> that do not loose any data in the process.
Haven't you read the previous threads? Implicit coercions are
dangerous, because they cause the system to resolve oper
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Gavin Sherry wrote:
> >> CREATE DATABASE also fills out a list in the same fashion =). I will
> >> however have a look at revising this patch to use DefElem later today.
>
> > Oh, I see that now. Which method do people prefer. We s
On Tuesday 16 April 2002 05:48 pm, you wrote:
> I want to change the date from a field in a tuple in a trigger_function
>
> create table example (
> my_date datetime
> ...
> );
>
> int na;
> char select[20];
>
> na = SPI_fnumber(trigdata->tg_relation->rd_att, "my_date");
> memset(select, 0
> The lines marked XXX are the ones that I enabled since yesterday, and
> would like to disable again:
>
> implicit | result|input|prosrc
> --+-+-+--
> no | varchar | int8
> I suspect that the main thing that will cause issues is removal of
> implicit coercions to text. For example, in 7.2 and before you can do
>
> test72=# select 'At the tone, the time will be ' || now();
> ?column?
>
Tom Lane wrote:
>
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > I had imagined that pg_dump would emit commands such as this:
>
> > CREATE SCHEMA foo
> > CREATE TABLE bar ( ... )
> > CREATE otherthings
> > ;
>
> > which is how I read the SQL standard. Are there plans to implement the
Sorry, just a question, maybe I should ask somewhere else.
Is there any difference (like cost, efficiency, execution paths...) between
this sql:
select b
from a
where b is not null
and this sql:
select b
from a
where not b is null
Just curious.
Thanks for your answer
Bosco
_
On Tue, 2002-04-16 at 03:20, Tatsuo Ishii wrote:
> In my understanding, our consensus was enabling multibyte support by
> default for 7.3. Any objection?
Is there currently some agreed plan for introducing standard
NCHAR/NVARCHAR types.
What does ISO/ANSI say about multybyteness of simple CHAR t
On Tue, 2002-04-16 at 07:01, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > How about this: We store the first 16 parameters in some fixed array for
> > fast access like now, and when you have more than 16 then 17 and beyond
> > get stored in some variable array in pg_proc.
>
Marc G. Fournier wrote:
> [...]
> Oh, and, of course, its running on v7.2.1 now, which means that VACUUMs no
> longer lock up the search ... last night, I had something like 367
> indexers pounding away at the database *and* a VACUUM running *and* did a
> search in <2 minutes (considering that is
Peter Eisentraut wrote:
> The string literals didn't contain any backslashes, so scanstr is
> operating in the best-case scenario here. But for arbitary binary data we
> need some escape mechanism, so I don't see much room for improvement
> there.
>
> It seems the real bottleneck is the excessiv
Tom Lane writes:
> The regression tests contain no very-long literals. The results I was
> referring to concerned cases with string (BLOB) literals in the
> hundreds-of-K range; it seems that the per-character loop in the flex
> lexer starts to look like a bottleneck when you have tokens that mu
On Tue, 16 Apr 2002, Rod Taylor wrote:
> b=# create table stuff (stuff_id serial unique);
> NOTICE: CREATE TABLE will create implicit sequence
> 'stuff_stuff_id_seq' for SERIAL column 'stuff.stuff_id'
> NOTICE: CREATE TABLE / UNIQUE will create implicit index
> 'stuff_stuff_id_key' for table 's
Christopher Kings-Lynne wrote:
> Hmmm...where's that file in the CVS where the release process is listed (or
> at least the places where version numbers need to be updated, etc.?)
>
> I can't find it...
src/tools/RELEASE_CHANGES. I suggested a RELEASE_ANNOUNCEMENT file for
URL's of place to ann
b=# create table stuff (stuff_id serial unique);
NOTICE: CREATE TABLE will create implicit sequence
'stuff_stuff_id_seq' for SERIAL column 'stuff.stuff_id'
NOTICE: CREATE TABLE / UNIQUE will create implicit index
'stuff_stuff_id_key' for table 'stuff'
CREATE
b=# create table stuff2 (stuff_id int
Jean-Paul ARGUDO wrote:
> > I've attached an updated version of Karel Zak's pg_qcache patch, which
> > adds PREPARE/EXECUTE support to PostgreSQL (allowing prepared SQL
> > statements).
>
> Woah :-))
>
> Thanks Neil! You may be remind of a thread in february, where I talked
> about a survey abo
On 7.1.x it definitely gets slower even for indexscans. e.g. 60 updates/sec
dropping to 30 then to 20 over time.
Is this fixed for 7.2?
If not, is it possible to make the pointer point to the latest row instead
of the most obsolete one, and having the newer rows point to the older
ones, inste
I want to change the date from a field in a tuple in a trigger_function
create table example (
my_date datetime
...
);
int na;
char select[20];
na = SPI_fnumber(trigdata->tg_relation->rd_att, "my_date");
memset(select, 0, sizeof(select));
sprintf(select, "1/1/2002");
newval = DirectFunc
Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> While trying to optimise a query I found that running VACUUM ANALYSE
> changed all the Index Scans to Seq Scans and that the only way to revert
> to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.
EXPLAIN ANALYZE output would b
On Tue, 16 Apr 2002, Tom Lane wrote:
> "Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> > Could some ppl test out archives.postgresql.org and let me know if they
> > notice any differences in speed?
>
> Yup. It's usable again! What did you do?
Got more RAM installed :) The archives have a buf
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> Could some ppl test out archives.postgresql.org and let me know if they
> notice any differences in speed?
Yup. It's usable again! What did you do?
regards, tom lane
---(end of broadcast)-
Hello,
While trying to optimise a query I found that running VACUUM ANALYSE
changed all the Index Scans to Seq Scans and that the only way to revert
to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.
Seq Scans are much slower for that specific query. Why does Postgres
switch t
Denis Perchine <[EMAIL PROTECTED]> writes:
> I was interested in this:
> Firebird's indexes are very dense because they compress both the prefix and
> the suffix of each key. Suffix compression is simply the elimination of
> trailing blanks or zeros, depending on the data type. Suffix compressio
On Tue, 16 Apr 2002, Marc G. Fournier wrote:
>
> Could some ppl test out archives.postgresql.org and let me know if they
> notice any differences in speed?
Marc,
A dramatic increase in performance.
Gavin
---(end of broadcast)---
TIP 1: subscri
mlw <[EMAIL PROTECTED]> writes:
> Now, what if we did it another way, copy the old version of the row into the
> new row and update the tuple in place?
I don't think we can get away with moving the extant tuple. If we did,
a concurrent scan that should have found the old tuple might miss it.
(Th
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> In my understanding, our consensus was enabling multibyte support by
> default for 7.3. Any objection?
>>
>> Uh, was it? I don't recall that. Do we have any numbers on the
>> performance overhead?
> See below.
Oh, okay, now I recall that thread. You
Hi All!
Is anyone else working on
pl/java?
We are still working on it. In a few weeks we may
have a testable version. (For the most fearless hackers, we have a test
version now :)
If anyone else is working on any implementation,
please contect us!
http://pljava.sourceforge.net/
Thank
Could some ppl test out archives.postgresql.org and let me know if they
notice any differences in speed?
Thanks ...
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL P
I just had an interesting idea. It sounds too easy to beleve, but hear me out
and correct me if I'm wrong.
Currently, during update, PostgreSQL takes the existing record, modifyies it,
and adds it as a new row. The previous record has a pointer to the new version.
If the row is updated twice, the
Sorry I should've read the FAQ. I've posted the patch to pgsql-patches.
Am Dienstag, 16. April 2002 13:49 schrieb Mario Weilguni:
> I've improved the contributed vacuumlo command, now it behaves like all
> other postgres command line utilites e.g. supports -U, -p, -h, -?, -v,
> password prompt an
I've improved the contributed vacuumlo command, now it behaves like all other
postgres command line utilites e.g. supports -U, -p, -h, -?, -v, password
prompt and has a "test mode". In test mode, no large objects are removed,
just reported.
What can I do now with the patch (355 lines)? Send it
Hi,
I was interested in this:
Firebird's indexes are very dense because they compress both the prefix and
the suffix of each key. Suffix compression is simply the elimination of
trailing blanks or zeros, depending on the data type. Suffix compression is
performed on each segment of a segmented
> > In my understanding, our consensus was enabling multibyte support by
> > default for 7.3. Any objection?
>
> Uh, was it? I don't recall that. Do we have any numbers on the
> performance overhead?
>
> regards, tom lane
See below.
Subject: Re: [HACKERS] Unicode combin
> I've attached an updated version of Karel Zak's pg_qcache patch, which
> adds PREPARE/EXECUTE support to PostgreSQL (allowing prepared SQL
> statements).
Woah :-))
Thanks Neil! You may be remind of a thread in february, where I talked
about a survey about migrating from Oracle 8.0 / NT4 to Po
Hmmm...where's that file in the CVS where the release process is listed (or
at least the places where version numbers need to be updated, etc.?)
I can't find it...
Chris
> -Original Message-
> From: Justin Clift [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 16 April 2002 3:23 PM
> To: Chr
Hi Chris,
I don't have time at the moment to start making the needed document. :(
Does anyone want to throw together the basics of it and put it somewhere
useful?
:-)
Regards and best wishes,
Justin Clift
Christopher Kings-Lynne wrote:
>
> I remember someone mentioning on the list that we
I remember someone mentioning on the list that we should collect a list of
places that refer to postgres so that we can update them for a new release.
I just submitted an update on Linux.com:
http://software.linux.com/projects/postgresql/?topic=323,324,325
That location should be added to the l
72 matches
Mail list logo
