Re: [HACKERS] Some array semantics issues

[Joe Conway]

Tom Lane wrote:

Given the just-committed changes to avoid having array_push/array_cat
generate non-spec lower bounds unnecessarily, do you still think it's
important to have a variant of array comparison that ignores lower
bounds?

ISTM that ignoring lower bounds is definitely something that violates
the principle of least surprise.  There was an ease-of-use argument
for it before, but now that we changed the other thing I think we don't
need such a kluge.


I agree. At this point, having an array with other than 1 as a lower 
bound takes a very conscious decision. I'd think that if you cared that 
much about the lower bound, you'd not want to ignore it when it comes to 
comparison.


Joe


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] order by, for custom types

[Tom Lane]

Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes:
> Wildcards cause things not to work as they should

> consider everything in [] brackets to be a possible choice and those  
> three:

> a = 1.2.3.4
> b = 1.[2,3].3.4
> c = 1.3.3.4

> a = b, b = c, but a <> c, I was told that because of that btree won't  
> work on my type. (on irc, that was AndrewSN as I recall).

Well, neither will sorting then.  If you can define a consistent sort
order, btree will work; if you can't, then you can't sort either.
AFAICS, with rules like the above you can't define a consistent <
operator.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] someone working to add merge?

[Bruce Momjian]

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > I agree --- an implementation that needs to use a table lock is
> > useless, and one with no primary key is too hard to implement and
> > also near useless.
> 
> Well, there were just a couple of people saying the opposite.
> 
> > I have update the TODO item to reflect this:
> >
> > * Add MERGE command that does UPDATE/DELETE, or on failure, INSERT
> >   (rules, triggers?)
> >
> >   To implement this cleanly requires that the table have a unique
> > index so duplicate checking can be easily performed.
> 
> We're still trying to work out the semantic relationship between MERGE 
> and REPLACE and what-we-actually-want.  This entry doesn't seem to take 
> that into account.

Right.  Once we are done I will update it.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: 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: [HACKERS] Some array semantics issues

[Tom Lane]

Greg Stark <[EMAIL PROTECTED]> writes:
> [ this is back up-thread a bit ]
> And changing that would make it harder to test just the contents of the array
> without having to match bounds as well. That is, You couldn't say "list =
> '{1,2}'" to test if the array contained 1,2. You would have to, well, I'm not
> even sure how you would test it actually. Maybe something kludgy like
> "'{}'::int[] || list = '{1,2}'" ?

Given the just-committed changes to avoid having array_push/array_cat
generate non-spec lower bounds unnecessarily, do you still think it's
important to have a variant of array comparison that ignores lower
bounds?

ISTM that ignoring lower bounds is definitely something that violates
the principle of least surprise.  There was an ease-of-use argument
for it before, but now that we changed the other thing I think we don't
need such a kluge.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] order by, for custom types

[Grzegorz Jaskiewicz]

On 2005-11-18, at 22:53, Tom Lane wrote:


Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes:

I have all operators required for b-tree, gist up here, and gist
index defined. But still "order by custom_type" won't work.


Define "won't work" ... what happens?



Wildcards cause things not to work as they should

consider everything in [] brackets to be a possible choice and those  
three:


a = 1.2.3.4
b = 1.[2,3].3.4
c = 1.3.3.4

a = b, b = c, but a <> c, I was told that because of that btree won't  
work on my type. (on irc, that was AndrewSN as I recall).




You don't need an index, but a b-tree operator class is a good idea.
Still, it should be possible to sort with only a "<" operator --- at
the moment anyway.  (I've been thinking about some ideas that would
effectively require a b-tree opclass to do sorting, so this might not
still be true in 8.2 ...)


I do have all operators required for btree, no operator class  
defined, every single operator. Btree requires some function apart  
from operators, this one is not defined, but I do have = operator as  
well.


--
GJ

"If we knew what we were doing, it wouldn't be called Research, would  
it?" - AE





---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] Improving count(*)

[Josh Berkus]

Alvaro,

> I guess there must be a query-rewriting mechanism for implementing
> materialized views.  With that in place we may be able to implement this
> other thing ...  Is anybody working on materialized views?

I have a bundle of academic code designed to do exactly this, if any hacker 
wants to take on the task of getting it into production shape.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Improving count(*)

[mark]

On Fri, Nov 18, 2005 at 03:46:42PM +, Richard Huxton wrote:
> Simon Riggs wrote:
> >One of the major complaints is always "Select count(*) is slow".
> Although there seem to have been plenty of ideas on this they all seem 
> to just provide a solution for the "whole table" case. It might be that 
> the solution provides other benefits, but for this one case it does seem 
> like a lot of work.

Or, it wasn't explained properly as to how the WHERE clause would
function.

The solution involving an index that has visibility information should
work fine with a WHERE clause. Only index rows that match the clause
are counted.

A solution enhancing the above mentioned indexes, to maintain a count
for whole index blocks, would allow whole index blocks that satisfy
the WHERE clause to be counted, assuming the whole index block is
visible in the current transaction.

Not to say these are the best ideas, or the only ideas - but it isn't
true that most of the solution presented only apply to the 'whole table'
case. The *simplest* solutions, apply only to the 'whole table' case. :-)

Cheers,
mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


---(end of broadcast)---
TIP 1: 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: [HACKERS] Optimizer bug in 8.1.0?

[Tom Lane]

Alexey Slynko <[EMAIL PROTECTED]> writes:
> Any suggestions?

Fix contrib/intarray to have some selectivity estimation procedures
for its operators?

Without any way to estimate the number of rows matching the @@
condition, the optimizer can hardly be expected to guess right...

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] PCTFree Results

[Jonah H. Harris]

Josh,

Do you have an 8.1 patch for this or only the 8.0.x?

On 9/22/05, Josh Berkus  wrote:
Folks,Well, it took a while but I finally have the results of Satoshi's PCTFreepatch back from the STP.  Bad news about the STP, see below ...Anyway, a series of DBT2 runs doesn't seem to show any advantage to PCTFree
over a 3-hour run with no vacuums:test#   pctfree full_page_writesnotpm303164  off off 1803303165  on  on  1847303166  on  off 1860303167  off on  1801
303168  off off 1838303169  on  on  1821303170  on  off 1846(again, check all results at http://www.testing.osdl.org/stp/##/
)The differences above are small enough to be in the "noise" factor of DBT2execution.   The good news is that it appears that stuff which has beendone since July has lessened the penalty for checkpoints somewhat; while
the maximum response time is still better on the full_page_writes=offsystems, the average throughput is no longer substantially different.Either that, or full_page_writes=off isn't working properly anymore.
If anyone has suggestions on different tests to run, or better stats tocompile, please speak up.Now, the bad news: the STP has had some failures and is down to *one*usable machine for testing.  I have, like, 160 performance tests backed up
which are never going to get run before we release 8.1.  I'm going to behitting up some major PostgreSQL sponsors for hardware donations, any helpis welcome.JoshJosh BerkusAglio Database Solutions
San Francisco---(end of broadcast)---TIP 1: 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: [HACKERS] order by, for custom types

[Tom Lane]

Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes:
> I have all operators required for b-tree, gist up here, and gist  
> index defined. But still "order by custom_type" won't work.

Define "won't work" ... what happens?

> I have kind of wild card masks in my type definition, so b-tree won't  
> work. But still, do I need to define b-tree index as such for "order  
> by" to work ?

You don't need an index, but a b-tree operator class is a good idea.
Still, it should be possible to sort with only a "<" operator --- at
the moment anyway.  (I've been thinking about some ideas that would
effectively require a b-tree opclass to do sorting, so this might not
still be true in 8.2 ...)

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Bug in predicate indexes?

[Joshua D. Drake]

This is a known (although perhaps not well documented) limitation of the
predicate testing logic.  You do not need a cast in the query, though,
only in the index's WHERE condition.
 


O.k. cool just wanted to make sure I wasn't the only one :).

Joshua D. Drake




regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
 




--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


---(end of broadcast)---
TIP 1: 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: [HACKERS] Improving count(*)

[Alvaro Herrera]

Tom Lane wrote:
> Richard Huxton  writes:
> > Might it be possible to apply rule-style rewriting to a clause of an 
> > ordinary select query? That is, is it prohibitively expensive to get PG 
> > to recognise
> >SELECT count(*) FROM big_table
> > and replace it with
> >SELECT sum(summary_count) FROM my_materialised_view
> 
> > This should allow you to have where-clauses and apply to a range of 
> > cases. What I fear is that checking to see if the rule applies will cost 
> >   too much on all those queries where it doesn't apply.
> 
> There is already code in the optimizer that does similar rewriting
> for min/max queries.   However, that's a hard-wired transformation.
> I don't see any very simple way to provide a user-configurable
> equivalent.

I guess there must be a query-rewriting mechanism for implementing
materialized views.  With that in place we may be able to implement this
other thing ...  Is anybody working on materialized views?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] someone working to add merge?

[Martijn van Oosterhout]

On Fri, Nov 18, 2005 at 05:30:34PM +0100, Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > I have update the TODO item to reflect this:
> >
> > * Add MERGE command that does UPDATE/DELETE, or on failure, INSERT
> >   (rules, triggers?)
> >
> >   To implement this cleanly requires that the table have a unique
> > index so duplicate checking can be easily performed.
> 
> We're still trying to work out the semantic relationship between MERGE 
> and REPLACE and what-we-actually-want.  This entry doesn't seem to take 
> that into account.

Right. From my reading of the spec (which someone posted here
somewhere) MERGE has no special rules regarding visibility. So it's
just as susceptable to "duplicate keys" and "lost updates" as any
current method. That doesn't dimish its usefulness, it's just not what
some people thought it was.

My current position is that since REPLACE seems to violate normal
transaction semantics (must not fail no matter what other backends are
doing) that any implementation will probably have to play fancy
footwork with locking and savepoints within a single statement. And
that's not MERGE.

I'd say implement SQL MERGE which doesn't have any really unusual
features. And seperately implement some kind of INSERT OR UPDATE which
works only for a table with a primary key.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgperPElZu5vJ.pgp
Description: PGP signature

Re: [HACKERS] order by, for custom types

[Greg Stark]

Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes:

> Hi folks
> 
> I have all operators required for b-tree, gist up here, and gist  index
> defined. But still "order by custom_type" won't work.

I think you need to create an "operator class" for ORDER BY to work. Someone
else may answer with more details.

> I have kind of wild card masks in my type definition, so b-tree won't  work.
> But still, do I need to define b-tree index as such for "order  by" to work ?
> Perhaps gist should be expanded so it would take care of "order by"  and
> "distinct" ?

This I don't understand. If ORDER BY will work then b-tree indexes will work
too. If your type is such that b-tree indexes don't make sense then neither
does ORDER BY.

-- 
greg


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Improving count(*)

[Gregory Maxwell]

On 11/18/05, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > In Sybase ASE (and I'm pretty sure the same is true in Microsoft SQL
> > Server) the leaf level of the narrowest index on the table is scanned,
> > following a linked list of leaf pages.  Leaf pages can be pretty dense
> > under Sybase, because they do use prefix compression.  A count(*)
> > on a table with 100 million rows is going to take a few minutes, but
> it
> > is going to be at least an order of magnitude faster than a data page
> > scan -- maybe two orders of magnitude faster.
>
> MS SQL server (pre 2005) is not an MVCC database, so it's not apples to
> apples with pg.  Many of the people who wander on this list and complain
> about count(*) either come from one of those or some other non-MVCC
> database or worse, a flat-file xbase type system.  A performance
> comparison between MS 2005 and pg would be much more interesting.
> Personally, I don't know what all the fuss is about [although I wouldn't
> complain about an optimization ;)].

count(*) WHERE 1  is indeed a corner case that few to no real
applications should care about... If we were having to choose between
improving that case and preserving the performance and maintainability
of PG then I think the discussion would already be over.

However, some great ideas have been proposed here which would not only
help in that case but would otherwise be quite useful.

*Inclusion of a 'MVCC inflight' bit in indexes which would allow
skipping MVCC checks in clumps of an index scan which have no pending
changes. This would further close the performance gap between PG and
non-MVCC databases for some workloads.
*Introduction of high performance table sampling, which would be
useful in many applications (including counting where there is a where
clause) as well as for testing and adhoc queries.
and
*a estimate_count() that provides the planner estimate, which would
return right away and provide what is really needed most of the time
people try to count(*) on a large table.

So, while this is a silly case to optimize for it's one where it
appears that the proposed solutions will make PG better all around.

---(end of broadcast)---
TIP 1: 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: [HACKERS] Improving count(*)

[Alvaro Herrera]

Merlin Moncure wrote:
> > In Sybase ASE (and I'm pretty sure the same is true in Microsoft SQL
> > Server) the leaf level of the narrowest index on the table is scanned,
> > following a linked list of leaf pages.  Leaf pages can be pretty dense
> > under Sybase, because they do use prefix compression.  A count(*)
> > on a table with 100 million rows is going to take a few minutes, but
> it
> > is going to be at least an order of magnitude faster than a data page
> > scan -- maybe two orders of magnitude faster.
> 
> MS SQL server (pre 2005) is not an MVCC database, so it's not apples to
> apples with pg.

Oh, also it was mentioned on pgsql-advocacy that InnoDB is MVCC.  If
that's the case, I wonder how do they do the count(*) thing?  Is it fast?


-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Improving count(*)

[Merlin Moncure]

> In Sybase ASE (and I'm pretty sure the same is true in Microsoft SQL
> Server) the leaf level of the narrowest index on the table is scanned,
> following a linked list of leaf pages.  Leaf pages can be pretty dense
> under Sybase, because they do use prefix compression.  A count(*)
> on a table with 100 million rows is going to take a few minutes, but
it
> is going to be at least an order of magnitude faster than a data page
> scan -- maybe two orders of magnitude faster.

MS SQL server (pre 2005) is not an MVCC database, so it's not apples to
apples with pg.  Many of the people who wander on this list and complain
about count(*) either come from one of those or some other non-MVCC
database or worse, a flat-file xbase type system.  A performance
comparison between MS 2005 and pg would be much more interesting.
Personally, I don't know what all the fuss is about [although I wouldn't
complain about an optimization ;)].

Merlin


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] CLUSTER and clustered indices

[Kevin Grittner]

That sounds very much like a CLUSTERED INDEX under Sybase ASE
(or the derivative Microsoft SQL Server).  In those products, when you
create a clustered index, the data pages are sorted according to the
index sequence, and are used as the leaf pages in the index.  A
clustered index does not have another leaf level.

>>> Simon Riggs <[EMAIL PROTECTED]>  >>>

As an aside, Index Organized Tables (IOTs) isn't just an Oracle term. 
They first used the term, but the concept had already been implemented
in both Tandem (value-ordered) and Teradata (hash-ordered) before this,
as well as numerous OLAP systems. The concept doesn't look to be
patented.

If anybody is looking for a justification for IOTs, the reduction in
table volume for large tables is very high. IOTs are the equivalent of
removing all of the leaf blocks of the clustered index.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] order by, for custom types

[Oleg Bartunov]

On Fri, 18 Nov 2005, Grzegorz Jaskiewicz wrote:


Hi folks

I have all operators required for b-tree, gist up here, and gist index 
defined. But still "order by custom_type" won't work.
I have kind of wild card masks in my type definition, so b-tree won't work. 
But still, do I need to define b-tree index as such for "order by" to work ?
Perhaps gist should be expanded so it would take care of "order by" and 
"distinct" ?


look contrib/ltree for reference (sql/ltree.sql)



Thanks.





Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] order by, for custom types

[Grzegorz Jaskiewicz]

Hi folks

I have all operators required for b-tree, gist up here, and gist  
index defined. But still "order by custom_type" won't work.
I have kind of wild card masks in my type definition, so b-tree won't  
work. But still, do I need to define b-tree index as such for "order  
by" to work ?
Perhaps gist should be expanded so it would take care of "order by"  
and "distinct" ?


Thanks.


--
GJ

"If we knew what we were doing, it wouldn't be called Research, would  
it?" - AE





---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Improving count(*)

[Tom Lane]

Richard Huxton  writes:
> Might it be possible to apply rule-style rewriting to a clause of an 
> ordinary select query? That is, is it prohibitively expensive to get PG 
> to recognise
>SELECT count(*) FROM big_table
> and replace it with
>SELECT sum(summary_count) FROM my_materialised_view

> This should allow you to have where-clauses and apply to a range of 
> cases. What I fear is that checking to see if the rule applies will cost 
>   too much on all those queries where it doesn't apply.

There is already code in the optimizer that does similar rewriting
for min/max queries.   However, that's a hard-wired transformation.
I don't see any very simple way to provide a user-configurable
equivalent.

regards, tom lane

---(end of broadcast)---
TIP 1: 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: [HACKERS] someone working to add merge?

[Peter Eisentraut]

Bruce Momjian wrote:
> I agree --- an implementation that needs to use a table lock is
> useless, and one with no primary key is too hard to implement and
> also near useless.

Well, there were just a couple of people saying the opposite.

> I have update the TODO item to reflect this:
>
>   * Add MERGE command that does UPDATE/DELETE, or on failure, INSERT
> (rules, triggers?)
>
> To implement this cleanly requires that the table have a unique
> index so duplicate checking can be easily performed.

We're still trying to work out the semantic relationship between MERGE 
and REPLACE and what-we-actually-want.  This entry doesn't seem to take 
that into account.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] MERGE vs REPLACE

[Tom Lane]

Bruce Momjian  writes:
> Oh, good point.  I was thinking just about concurrent MERGEs.  However,
> it is more complicated than that.  By definitaion you can not see
> changes from other transactions while your statement is being run (even
> if you increment CommandCounter), so to be atomic, you would still see
> the row even though some other transaction had deleted it.

We would have to use the same semantics we use now for read-committed
UPDATE, that is look at the latest version of the row even though this
would not normally be visible to the transaction's snapshot.

In the case of a serializable transaction, no doubt we should fail if
any concurrent change actually happens.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Improving count(*)

[Richard Huxton]

Simon Riggs wrote:

One of the major complaints is always "Select count(*) is slow".


Although there seem to have been plenty of ideas on this they all seem 
to just provide a solution for the "whole table" case. It might be that 
the solution provides other benefits, but for this one case it does seem 
like a lot of work.


Might it be possible to apply rule-style rewriting to a clause of an 
ordinary select query? That is, is it prohibitively expensive to get PG 
to recognise

  SELECT count(*) FROM big_table
and replace it with
  SELECT sum(summary_count) FROM my_materialised_view

This should allow you to have where-clauses and apply to a range of 
cases. What I fear is that checking to see if the rule applies will cost 
 too much on all those queries where it doesn't apply.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] Optimizer bug in 8.1.0?

[Alexey Slynko]

Hi,

I have database with two tables:

test1=# \d messages
Table "public.messages"
 Column  |   Type| Modifiers
--+---+---
msg_id   | integer   | not null
sections | integer[] |
Indexes:
   "messages_pkey" PRIMARY KEY, btree (msg_id)
   "messages_sect_idx" gist (sections gist__intbig_ops)


and

test1=# \d message_parts
  Table "public.message_parts"
 Column   |   Type   | Modifiers
---+--+---
msg_id| integer  |
index_fts | tsvector |
Indexes:
   "a_gist_key" gist (index_fts)
   "message_parts_msg_id" btree (msg_id)

Number of records are:

test1=# SELECT count(*) from messages ;
count
---
41483
(1 row)

and

test1=# SELECT count(*) from message_parts ;
count 


511136
(1 row)

Then, try to execute query:test1=# EXPLAIN ANALYZE SELECT * from 
messages m1, message_parts m2 where m1.msg_id = m2.msg_id and 
m1.sections @@ '30210' and m2.index_fts @@ 'mar';
QUERY 
PLAN 
-
Nested Loop  (cost=11.07..608.20 rows=1 width=481) (actual 
time=744.008..5144.721 rows=4 loops=1)
  ->  Bitmap Heap Scan on messages m1  (cost=3.15..118.46 rows=41 
width=38) (actual time=1.734..5.737 rows=348 loops=1)

Filter: (sections @@ '30210'::query_int)
->  Bitmap Index Scan on messages_sect_idx  (cost=0.00..3.15 
rows=41 width=0) (actual time=1.655..1.655 rows=348 loops=1)

  Index Cond: (sections @@ '30210'::query_int)
  ->  Bitmap Heap Scan on message_parts m2  (cost=7.92..11.93 rows=1 
width=443) (actual time=14.752..14.752 rows=0 loops=348)

Recheck Cond: ("outer".msg_id = m2.msg_id)
Filter: (index_fts @@ '''mar'''::tsquery)
->  BitmapAnd  (cost=7.92..7.92 rows=1 width=0) (actual 
time=14.743..14.743 rows=0 loops=348)
  ->  Bitmap Index Scan on message_parts_msg_id  
(cost=0.00..2.88 rows=252 width=0) (actual time=0.026..0.026 rows=6 
loops=348)

Index Cond: ("outer".msg_id = m2.msg_id)
  ->  Bitmap Index Scan on a_gist_key  (cost=0.00..4.79 
rows=511 width=0) (actual time=14.966..14.966 rows=1762 loops=342)

Index Cond: (index_fts @@ '''mar'''::tsquery)
Total runtime: 5144.859 ms
(14 rows)

And if I turn enable_bitmapscan = off, then:

test1=# SET enable_bitmapscan = off;

test1=# EXPLAIN ANALYZE SELECT * from messages m1, message_parts m2 
where m1.msg_id = m2.msg_id and m1.sections @@ '30210' and 
m2.index_fts @@ 'mar';
  QUERY 
PLAN   
-
Hash Join  (cost=158.07..2128.36 rows=1 width=481) (actual 
time=65.873..203.738 rows=4 loops=1)

  Hash Cond: ("outer".msg_id = "inner".msg_id)
  ->  Index Scan using a_gist_key on message_parts m2  
(cost=0.00..1967.73 rows=511 width=443) (actual time=0.170..200.361 
rows=481 loops=1)

Index Cond: (index_fts @@ '''mar'''::tsquery)
Filter: (index_fts @@ '''mar'''::tsquery)
  ->  Hash  (cost=157.96..157.96 rows=41 width=38) (actual 
time=2.489..2.489 rows=348 loops=1)
->  Index Scan using messages_sect_idx on messages m1  
(cost=0.00..157.96 rows=41 width=38) (actual time=0.052..2.020 rows=348 
loops=1)

  Index Cond: (sections @@ '30210'::query_int)
  Filter: (sections @@ '30210'::query_int)
Total runtime: 203.857 ms
(10 rows)

Test suite can be found at http://www.pgsql.ru/optimizer_bug.tar.gz 
(WARNING: 22 MB)


Any suggestions?






---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Anyone want to fix plperl for null array elements?

[Andrew Dunstan]

Michael Fuhr wrote:


On Thu, Nov 17, 2005 at 08:41:51PM -0500, Tom Lane wrote:
 


I think plperl should be fixed to translate undef to NULL when returning
an array, but currently it translates to an empty string:
   



I'll take a look at this if nobody else steps up.  It might just
be a minor change to this part of plperl.c:

210 "else " \
211 "{ " \
212 "  my $str = qq($elem); " \
213 "  $str =~ s/([\"])/$1/g; " \
214 "  $res .= qq(\"$str\"); " \
215 "} " \

 



Yeah.

I have the fix below which I will apply shortly. Demo:

perltest=# CREATE OR REPLACE function returns_array() returns text[] as $$
perltest$# return ['a,b','c"d',undef,'e-f']; $$ LANGUAGE plperl;
CREATE FUNCTION
perltest=# select returns_array();
 returns_array  


{"a,b","c\"d",NULL,e-f}



There might be some problems going in the other direction, too;
I haven't tried.  Anybody feeling eager to fix this?
   



Does the current implementation provide automatic conversion to a
Perl array for inbound values?  Unless I'm missing something that
entire problem might still need to be solved.

 



Correct, we get the string representation, which is ugly. We certainly 
want to change that for 8.2 so we get an arrayref. And this makes it 
more urgent. demo:


perltest=# create or replace function dump_array(text[]) returns text 
language plperlu as $$ use Data::Dumper; return Dumper([EMAIL PROTECTED]); $$;

CREATE FUNCTION
perltest=# select dump_array(ARRAY(select datname from pg_database));
   dump_array
---

$VAR1 = [
 '{postgres,perltest,template1,template0}'
   ];


cheers

andrew

Index: plperl.c
===
RCS file: /cvsroot/pgsql/src/pl/plperl/plperl.c,v
retrieving revision 1.94
diff -c -r1.94 plperl.c
*** plperl.c18 Oct 2005 17:13:14 -  1.94
--- plperl.c18 Nov 2005 13:13:31 -
***
*** 207,218 
   "{ " \
   "  $res .= _plperl_to_pg_array($elem); " \
   "} " \
!   "else " \
   "{ " \
   "  my $str = qq($elem); " \
   "  $str =~ s/([\"])/$1/g; " \
   "  $res .= qq(\"$str\"); " \
   "} " \
   "  } " \
   "  return qq({$res}); " \
   "} "
--- 207,222 
   "{ " \
   "  $res .= _plperl_to_pg_array($elem); " \
   "} " \
!   "elsif (defined($elem)) " \
   "{ " \
   "  my $str = qq($elem); " \
   "  $str =~ s/([\"])/$1/g; " \
   "  $res .= qq(\"$str\"); " \
   "} " \
+   "else " \
+   "{ "\
+   "  $res .= 'NULL' ; " \
+   "} "\
   "  } " \
   "  return qq({$res}); " \
   "} "



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] Bug in predicate indexes?

[Tom Lane]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> It appears that predicate indexes won't uses indexes on int8 columns 
> unless they are casted:

This is a known (although perhaps not well documented) limitation of the
predicate testing logic.  You do not need a cast in the query, though,
only in the index's WHERE condition.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Optional postgres database not so optional in 8.1

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> I now notice that "pg_ctl -w start" fails if the postgres db is missing. 
> I am not sure that changing pg_ctl to use this rather than template1 was 
> a good thing, and it can't be overridden. I suggest we revert that 
> particular change - it seems to me to confer little to no benefit, 
> unlike the case with createdb etc.

pg_ctl -w is already incredibly fragile because it needs a working
password-free login name.  Rather than worrying about whether the
database name exists, what we ought to do is invent the long-awaited
"ping" extension to the postmaster protocol --- something that would
just ask "are you up and ready to accept connections" without having
to specify a valid user *or* database name.

You can sort of do this today if you are willing to examine the error
message that comes back from the postmaster, but I think it'd be cleaner
to have an official protocol extension.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] MERGE vs REPLACE

[Bruce Momjian]

Oh, good point.  I was thinking just about concurrent MERGEs.  However,
it is more complicated than that.  By definitaion you can not see
changes from other transactions while your statement is being run (even
if you increment CommandCounter), so to be atomic, you would still see
the row even though some other transaction had deleted it.

I think we avoid that now because UPDATE, (which is a DELETE then
INSERT) chains the tuples together so others see the activity happening.

Seems like we are going to have to peek at rows like we do now for
INSERT and peek at index rows, if I remember correctly.  I can't think
of any other place in the code where we loop around other backend's
activity like this.

This could be tricky.

---

Dennis Bjorklund wrote:
> On Thu, 17 Nov 2005, Bruce Momjian wrote:
> 
> > Unless you have a table lock, INSERT has to be before UPDATE, think
> > UPDATE, UPDATE (both fail), INSERT, INSERT.
> 
> No matter what operation you start with you need a loop that try 
> insert/update until one of them succeed like in this example:
> 
> http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
> 
> Without a loop you might not get to execute neither the insert nor the 
> update. Why? Think about this example:
> 
> BEGIN
> 
> INSERT  <- fail because there is a row already
> 
> <- before we manage to do the update someone
>delete the row (which we can see in the
>default transaction isolation level)
> 
> UPDATE  <- fail because there is no row so we will loop
>and try the insert again
> 
> <- before we manage to do the insert someone else does
>an insert
> 
> INSERT  <- fail because there is a row already
> 
> <- before we manage to do the update someone
>delete the row 
> 
> 
> 
> You might need to loop any number of times before you manage to perform
> one of the two operations. Which operation you should start with depends
> on which of the two cases is the common one.
> 
> -- 
> /Dennis Bj?rklund
> 
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Optional postgres database not so optional in 8.1

[Andrew Dunstan]

Tom Lane wrote:


Andrew Dunstan <[EMAIL PROTECTED]> writes:
 

I now notice that "pg_ctl -w start" fails if the postgres db is missing. 
I am not sure that changing pg_ctl to use this rather than template1 was 
a good thing, and it can't be overridden. I suggest we revert that 
particular change - it seems to me to confer little to no benefit, 
unlike the case with createdb etc.
   



pg_ctl -w is already incredibly fragile because it needs a working
password-free login name.  Rather than worrying about whether the
database name exists, what we ought to do is invent the long-awaited
"ping" extension to the postmaster protocol --- something that would
just ask "are you up and ready to accept connections" without having
to specify a valid user *or* database name.

You can sort of do this today if you are willing to examine the error
message that comes back from the postmaster, but I think it'd be cleaner
to have an official protocol extension.
 




Actually, it looks like pg_ctl already does this:

   if ((conn = PQsetdbLogin(NULL, portstr, NULL, NULL,
"postgres", NULL, NULL)) != NULL &&
   (PQstatus(conn) == CONNECTION_OK ||
(strcmp(PQerrorMessage(conn),
PQnoPasswordSupplied) == 0)))
   {
   PQfinish(conn);
   success = true;
   break;
   }


cheers

andrew


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [pgsql-advocacy] Call for sample databases

[Javier Soltero]

Thanks for the clarification. I suspected as much. :(
___
Javier Soltero
Hyperic  |  www.hyperic.net
o- 415 738 2566  |  c- 415 305 8733
[EMAIL PROTECTED]
___

On Nov 17, 2005, at 6:06 PM, Christopher Kings-Lynne wrote:

Well yeah it'd be open source.  If you license it BSD then anyone  
can do anything with it.  In your situation I'd probably recommend  
that you don't release it...  I'd also be hesitant to accept it :)



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] [pgsql-advocacy] Call for sample databases

[Javier Soltero]

Hi Chris,

Hyperic would be happy to donate an exported version of our pg  
database for a fairly loaded environment. Just so I'm clear, there's  
no implication of making the schema itself open source or anything  
like that, right? We're perfectly cool with letting people look at it  
(they can do that simply by downloading our product), but I want to  
make sure that there's no other implications to making this available.


The schema is about 80 tables, and will be a good example of an OLTP  
database for PG 8.1. Hopefully it will be helpful to others. Let me  
know how we can make such an export available to you. The file's big.


-javier
___
Javier Soltero
Hyperic  |  www.hyperic.net
o- 415 738 2566  |  c- 415 305 8733
[EMAIL PROTECTED]
___

On Nov 16, 2005, at 8:18 PM, Christopher Kings-Lynne wrote:


Hi guys,

I've set up a new sample databases project:

http://pgfoundry.org/projects/dbsamples/

If any of you have sample databases (schema + data, pg_dump format)  
that you are willing to share under the BSD license, please send  
'em to me so I can host them on the project.


You might also find interesting the new icons that Niko of the  
pgAdmin project has donated to the work.  Check the 'Icons'  
category here: http://pgfoundry.org/docman/?group_id=189  They  
are _awesome_.


Cheers,

Chris



---(end of  
broadcast)---

TIP 1: 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



---(end of broadcast)---
TIP 1: 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: [HACKERS] Optional postgres database not so optional in 8.1

[Andrew Dunstan]

I now notice that "pg_ctl -w start" fails if the postgres db is missing. 
I am not sure that changing pg_ctl to use this rather than template1 was 
a good thing, and it can't be overridden. I suggest we revert that 
particular change - it seems to me to confer little to no benefit, 
unlike the case with createdb etc.


cheers

andrew



Andrew Dunstan wrote:




Andrew Dunstan wrote:



It does seem a bit inconsistent that psql wouldn't connect to the
specified database in order to do -l, if one is specified.
Anyone want to look and see if it's easy to change?
 




  options.action == ACT_LIST_DB  && options.dbname == NULL ? 
"postgres" : options.dbname




Tested, and worked fine.

Committed on Head and 8.1 branches.

cheers

andrew

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Improving count(*)

[Steve Wampler]

Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> 
>>From here, another proposal. We have a GUC called count_uses_estimate
>>that is set to off by default. If set to true, then a count(*) will use
>>the planner logic to estimate number of rows in the table and return
>>that as the answer, rather than actually count the row.
> 
> 
> Ugh.  Why not just provide a function to retrieve the planner estimate,
> but *not* call it count(*)?  It would fit nicely with the contrib/dbsize
> stuff (or I should say, the stuff formerly in dbsize...)

That would completely remove my needs for a fast count() - all I want
is a way to quickly estimate table sizes for an operator's display.  Tom's
suggestion would provide exactly that.

-- 
Steve Wampler -- [EMAIL PROTECTED]
The gods that smiled on your birth are now laughing out loud.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Improving count(*)

[Tino Wildenhain]

Zeugswetter Andreas DCP SD schrieb:

Since that costs, I guess I would make it optional and combine it


with 


materialized views that are automatically used at runtime, and can


at 


the same time answer other aggregates or aggregates for groups.
create materialized view xx_agg enable query rewrite as select 
count(*), sum (col1) from xx [group by col2];




I wonder how many times you really need a count(*) w/o where clause.
If I understand you correctly you are trying to optimize just this one


case?

I guess you have not read to the end. A materialized view with a group
by 
as indicated in the example is able to answer all sorts of queries

with or without where clauses ( e.g. ... where col2 = 'x').


But wouldn't that mean I need a materialized view (does we have
that now or do I need to play the usual games with triggers?)
for every possible where condition?

---(end of broadcast)---
TIP 1: 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: [HACKERS] Improving count(*)

[Zeugswetter Andreas DCP SD]

> > Since that costs, I guess I would make it optional and combine it
with 
> > materialized views that are automatically used at runtime, and can
at 
> > the same time answer other aggregates or aggregates for groups.
> > create materialized view xx_agg enable query rewrite as select 
> > count(*), sum (col1) from xx [group by col2];
> > 
> 
> I wonder how many times you really need a count(*) w/o where clause.
> If I understand you correctly you are trying to optimize just this one
case?

I guess you have not read to the end. A materialized view with a group
by 
as indicated in the example is able to answer all sorts of queries
with or without where clauses ( e.g. ... where col2 = 'x').

Andreas

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] MERGE vs REPLACE

[Zeugswetter Andreas DCP SD]

> Unless you have a table lock, INSERT has to be before UPDATE, think
UPDATE, UPDATE (both fail), INSERT, INSERT.

> > update
> > if no rows updated
> >   insert
> >   if duplicate key
> > update
> > if no rows updated goto insert

That is why you have the loop. This is not a problem with above code,
because only one insert succeeds
while the others then do the update.

Andreas

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Improving count(*)

[Tino Wildenhain]

Zeugswetter Andreas DCP SD schrieb:
The instant someone touches a block it would no longer be marked as 
frozen (vacuum or analyze or other is not required) and count(*)


would 


visit the tuples in the block making the correct decision at that


time.


Hmm, so the idea would be that if a block no longer contained any


tuples hidden from any active transaction,


you could store the count and skip reading that page.



I like the approach of informix and maxdb, that can tell the count(*)
instantly without looking at index leaf or data pages.

Imho we could do that with a central storage of count(*) even with mvcc.
The idea is a base value for count(*) and corrective values per open
xid.
To tell the count you add all corrective values whose xid is visible in
snapshot.
Each backend is responsibe for compacting xid counters below min open
xid.
Periodically (e.g. at checkpoint time) you compact (aggregate committed
xid counters 
into the base value) and persist the count.


Since that costs, I guess I would make it optional and combine it with
materialized 
views that are automatically used at runtime, and can at the same time
answer other 
aggregates or aggregates for groups. 
create materialized view xx_agg enable query rewrite as select count(*),

sum (col1) from xx
[group by col2];



I wonder how many times you really need a count(*) w/o where clause.
If I understand you correctly you are trying to optimize just this
one case?

Regards
Tino

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Improving count(*)

[Zeugswetter Andreas DCP SD]

> > The instant someone touches a block it would no longer be marked as 
> > frozen (vacuum or analyze or other is not required) and count(*)
would 
> > visit the tuples in the block making the correct decision at that
time.
> 
> Hmm, so the idea would be that if a block no longer contained any
tuples hidden from any active transaction,
> you could store the count and skip reading that page.

I like the approach of informix and maxdb, that can tell the count(*)
instantly without looking at index leaf or data pages.

Imho we could do that with a central storage of count(*) even with mvcc.
The idea is a base value for count(*) and corrective values per open
xid.
To tell the count you add all corrective values whose xid is visible in
snapshot.
Each backend is responsibe for compacting xid counters below min open
xid.
Periodically (e.g. at checkpoint time) you compact (aggregate committed
xid counters 
into the base value) and persist the count.

Since that costs, I guess I would make it optional and combine it with
materialized 
views that are automatically used at runtime, and can at the same time
answer other 
aggregates or aggregates for groups. 
create materialized view xx_agg enable query rewrite as select count(*),
sum (col1) from xx
[group by col2];

Your page flag storage could possibly also be used for btree access, to
short circuit
the heap visibility lookup (e.g. for pages where all rows are visible
(vacuumed)).
I think that your proposal is too complex if it is not used to also
improve other
performance areas. 

Andreas

---(end of broadcast)---
TIP 1: 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

[HACKERS] delete trigger

[Aftab Alam]

Hello,
I want to create a trigger in PostgresSQL 
In trigger, Before inserting the record, if 
data is already in the table, the trigger fire the mesaage that data is already 
there, and after that trigger ckeck for next insert statement.
Pleae help me in this 
regard , kindly 
 reply.
Regards, 
_ 
Aftab Alam 

Re: [HACKERS] CLUSTER and clustered indices

[Simon Riggs]

On Thu, 2005-11-17 at 21:57 -0300, Alvaro Herrera wrote:

> Personally I'd prefer to see index-ordered heaps, where the heap is
> itself an index, so the ordering it automatically kept.

Agreed. (I think thats case-closed on the previous proposal.)

As an aside, Index Organized Tables (IOTs) isn't just an Oracle term. 
They first used the term, but the concept had already been implemented
in both Tandem (value-ordered) and Teradata (hash-ordered) before this,
as well as numerous OLAP systems. The concept doesn't look to be
patented.

If anybody is looking for a justification for IOTs, the reduction in
table volume for large tables is very high. IOTs are the equivalent of
removing all of the leaf blocks of the clustered index.

Best Regards, Simon Riggs


---(end of broadcast)---
TIP 6: explain analyze is your friend