Re: [HACKERS] TODO list updates

[Heikki Linnakangas]

On 16 October 2015 18:20:59 EEST, Bruce Momjian  wrote:
>I think on-disk bitmap indexes would only beat GIN indexes in a
>read-only database on low-cardinality columns.  For example, if you had
>a purchase_log table and wanted to know all the "blue" and "large"
>items
>sold at a specific store, I can see on-disk bitmap indexes doing well
>there.  If you added the product number, or needed read/write, I think
>GIN would win.  I just don't think we have enough deployments who need
>what on-disk bitmap are best at.

My take on this is that we effectively already have bitmap indexes: it's called 
GIN. We could make the posting list compression even better, currently a TID is 
compressed at best to a single byte, while in a bitmap index it could go down 
to one bit, or even less. But that's just a matter of improving the compression 
algorithm, making it more bitmapy, and could be done as a fairly isolated 
change in GIN code.

Besides being more dense, there are some other tricks often associated with 
bitmap indexes. Instead of storing a bitmap/posting list per each unique value, 
you could store one for a range of values. That's useful e.g. for storing 
floats, where you don't have many exact duplicate values, but you could get a 
dense index by treating all values in range 0.0-10.0 as one entry, all values 
in 10.0-50.0 as another,  and so forth. Yet another trick is to have one bitmap 
for all values > 0.0, another for all values >10.0, and so forth. With that, 
you can satisfy any BETWEEN query by scanning just two bitmaps/posting lists: 
the one for the lower bound and the one for the upper bound. The matching 
tuples are the ones that are present in the first, but not the latter posting 
list. But that's also not a whole new index type. GIN could do all that, if 
someone just wrote an opclass for it.

- Heikki


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Amit Kapila]

On Fri, Oct 16, 2015 at 8:34 AM, Bruce Momjian  wrote:

> I have spend the past few days updating our TODO list, removing
> completed and now-unnecessary items:
>
> https://wiki.postgresql.org/wiki/Todo
>
>
Thanks.  It can help encourage many new entrants to community.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] TODO list updates

[Bruce Momjian]

On Fri, Oct 16, 2015 at 02:50:04PM +0530, Amit Kapila wrote:
> On Fri, Oct 16, 2015 at 8:34 AM, Bruce Momjian  wrote:
> 
> I have spend the past few days updating our TODO list, removing
> completed and now-unnecessary items:
> 
>         https://wiki.postgresql.org/wiki/Todo
> 
> 
> 
> Thanks.  It can help encourage many new entrants to community.

I reduced the number of items from 466 to 410.

Probably the most controvertial change was to move on-disk bitmap
indexes to the "not wanted" section, though I kept the links in case we
change our minds.  I just can't see how they would be a win with GIN and
in-memory bitmaps.  (I don't think BRIN indexes help for on-disk bitmap
use-cases, do they?)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Bruce Momjian]

On Fri, Oct 16, 2015 at 12:00:11PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > I have spend the past few days updating our TODO list, removing
> > completed and now-unnecessary items:
> > 
> > https://wiki.postgresql.org/wiki/Todo
> 
> Thanks.  We have "TodoDone" pages for items that were done in specific
> releases, but only for 8.4, 9.0 and 9.1.  I guess it was too much work
> to create those.  (See https://wiki.postgresql.org/wiki/Category:Todo ).

Oh, I had forgotten about that.  In some cases I knew the release that
completed the item, but in most cases the item had a different approach
which was not taken, or was superceeded by a better solution.

> I added a link to the latest posted version of bitmap indexes by Abhijit
> Menon-Sen, for completeness.

Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Bruce Momjian]

On Fri, Oct 16, 2015 at 12:02:03PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > Are you suggesting I remove those links?  It is kind of odd to have
> > links to patches for features we don't want, or just keep it?
> 
> No, quite the contrary -- I think the links allow some other person
> research the issue including the history of patches and discussion, and
> decide for themselves whether the feature is right to be rejected.

I think on-disk bitmap indexes would only beat GIN indexes in a
read-only database on low-cardinality columns.  For example, if you had
a purchase_log table and wanted to know all the "blue" and "large" items
sold at a specific store, I can see on-disk bitmap indexes doing well
there.  If you added the product number, or needed read/write, I think
GIN would win.  I just don't think we have enough deployments who need
what on-disk bitmap are best at.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Alvaro Herrera]

Bruce Momjian wrote:
> I have spend the past few days updating our TODO list, removing
> completed and now-unnecessary items:
> 
>   https://wiki.postgresql.org/wiki/Todo

Thanks.  We have "TodoDone" pages for items that were done in specific
releases, but only for 8.4, 9.0 and 9.1.  I guess it was too much work
to create those.  (See https://wiki.postgresql.org/wiki/Category:Todo ).

I added a link to the latest posted version of bitmap indexes by Abhijit
Menon-Sen, for completeness.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Alvaro Herrera]

Bruce Momjian wrote:

> Are you suggesting I remove those links?  It is kind of odd to have
> links to patches for features we don't want, or just keep it?

No, quite the contrary -- I think the links allow some other person
research the issue including the history of patches and discussion, and
decide for themselves whether the feature is right to be rejected.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Alvaro Herrera]

Bruce Momjian wrote:

> Probably the most controvertial change was to move on-disk bitmap
> indexes to the "not wanted" section, though I kept the links in case we
> change our minds.  I just can't see how they would be a win with GIN and
> in-memory bitmaps.

Yeah, I recall we discussed bitmap indexes a lot and we found there
wasn't a lot of room to use them because GIN is just too good, it seems.
Also, the patches that were developed had a number of issues.  Anyone
wanting to develop bitmap indexes would probably be better off starting
from scratch.

> (I don't think BRIN indexes help for on-disk bitmap use-cases, do
> they?)

No, they don't.  I expect BRIN to be very bad in a limited domain (which
is where bitmap indexes are supposed to shine), except under specific
conditions.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Bruce Momjian]

On Fri, Oct 16, 2015 at 11:43:10AM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > Probably the most controvertial change was to move on-disk bitmap
> > indexes to the "not wanted" section, though I kept the links in case we
> > change our minds.  I just can't see how they would be a win with GIN and
> > in-memory bitmaps.
> 
> Yeah, I recall we discussed bitmap indexes a lot and we found there
> wasn't a lot of room to use them because GIN is just too good, it seems.
> Also, the patches that were developed had a number of issues.  Anyone
> wanting to develop bitmap indexes would probably be better off starting
> from scratch.

Yes, that was my conclusion too.  We have played with the on-disk bitmap
idea for a long time, but GIN has gotten very good in that time.

Are you suggesting I remove those links?  It is kind of odd to have
links to patches for features we don't want, or just keep it?

> > (I don't think BRIN indexes help for on-disk bitmap use-cases, do
> > they?)
> 
> No, they don't.  I expect BRIN to be very bad in a limited domain (which
> is where bitmap indexes are supposed to shine), except under specific
> conditions.

Yes, that was my conclusion too.  Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO list updates

[Bruce Momjian]

I have spend the past few days updating our TODO list, removing
completed and now-unnecessary items:

https://wiki.postgresql.org/wiki/Todo

I encourage others to also update the list to make it more accurate. 
Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Bruce Momjian]

Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
  In reading through the TODO list, I noticed a few things that I think
  are done, may be done, or may be partially done.  See below.
  Thoughts?  ...Robert
 
  Add missing operators for geometric data types
  - this is at least partly done.  not sure if it is entirely done.
 
 I think you are thinking of Teodor's point_ops patch, but what that
 did was add GIST index support for some existing geometric operators.
 This TODO item suffers from the all-too-common sin of being uselessly
 vague; it doesn't identify what operators it thinks are missing.

The full TODO item text is:

Add missing operators for geometric data types

Some geometric types do not have the full suite of geometric
operators, e.g. box @ point

* point_ops for GiST 

but we now have the @ operator mentioned:

 pg_catalog | @   | box   | point  | boolean | 
contains?

so I have removed this TODO item.

  Add UNIQUE capability to non-btree indexes
  - This is one application of exclusion constraints, so I'd say this is done.
 
 I think exclusion constraints address this as much as it needs to be
 addressed for GIST and GIN, neither of which have equality as a core
 concept.  Hash, however, does have that.  So I'd vote for narrowing the
 entry to support UNIQUE natively in hash indexes and moving it to the
 hash-index section.

Agreed, moved.

  Clean up VACUUM FULL's klugy transaction management
  - I think this is moot with the new cluster-based VF.
 
 Right, that one's either done or no longer relevant depending on how you
 want to look at it.

Agreed, removed.

 There is another TODO item that I was just struck by while reading your
 response to Joseph Adams:
 
   Fix system views like pg_stat_all_tables to use set-returning
   functions, rather than views of per-column functions
 
 What is the point of this proposal?  The reason for the per-column function
 implementation was to allow people to slice and dice the underlying data
 their own way.  Changing to monolithic SRFs would make that harder, and
 I don't see that it's buying anything especially useful.  I'd vote for
 taking this off unless someone can explain why it's an improvement.

Agreed, removed.  I wasn't aware why we implemented this as slices.  I
now assume it was for performance reasons.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Bruce Momjian]

Robert Haas wrote:
 In reading through the TODO list, I noticed a few things that I think
 are done, may be done, or may be partially done.  See below.
 Thoughts?  ...Robert
 
 Add missing operators for geometric data types
 - this is at least partly done.  not sure if it is entirely done.
 
 Add OR REPLACE to CREATE LANGUAGE
 - Done.

TODO updated.

 Add PQescapeIdentifierConn()
 - Done, as PQescapeIdentifier().

TODO updated.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Hitoshi Harada]

2010/3/27 Tom Lane t...@sss.pgh.pa.us:
 Robert Haas robertmh...@gmail.com writes:
 In reading through the TODO list, I noticed a few things that I think
 are done, may be done, or may be partially done.  See below.
 Thoughts?  ...Robert

 Implement full support for window framing clauses.
 - Not sure if we made any progress on this or not.

 We made some progress for 9.0, but there's still a lot of unimplemented
 territory.

TODO updated.



-- 
Hitoshi Harada

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO list updates

[Robert Haas]

In reading through the TODO list, I noticed a few things that I think
are done, may be done, or may be partially done.  See below.
Thoughts?  ...Robert

Add missing operators for geometric data types
- this is at least partly done.  not sure if it is entirely done.

Add OR REPLACE to CREATE LANGUAGE
- Done.

Implement full support for window framing clauses.
- Not sure if we made any progress on this or not.

Add PQescapeIdentifierConn()
- Done, as PQescapeIdentifier().

Add UNIQUE capability to non-btree indexes
- This is one application of exclusion constraints, so I'd say this is done.

[GIN] Support empty indexed values (such as zero-element arrays) properly
- I think this might be done but I'm not sure.

Clean up VACUUM FULL's klugy transaction management
- I think this is moot with the new cluster-based VF.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Tom Lane]

Robert Haas robertmh...@gmail.com writes:
 In reading through the TODO list, I noticed a few things that I think
 are done, may be done, or may be partially done.  See below.
 Thoughts?  ...Robert

 Add missing operators for geometric data types
 - this is at least partly done.  not sure if it is entirely done.

I think you are thinking of Teodor's point_ops patch, but what that
did was add GIST index support for some existing geometric operators.
This TODO item suffers from the all-too-common sin of being uselessly
vague; it doesn't identify what operators it thinks are missing.

 Implement full support for window framing clauses.
 - Not sure if we made any progress on this or not.

We made some progress for 9.0, but there's still a lot of unimplemented
territory.

 Add UNIQUE capability to non-btree indexes
 - This is one application of exclusion constraints, so I'd say this is done.

I think exclusion constraints address this as much as it needs to be
addressed for GIST and GIN, neither of which have equality as a core
concept.  Hash, however, does have that.  So I'd vote for narrowing the
entry to support UNIQUE natively in hash indexes and moving it to the
hash-index section.

 [GIN] Support empty indexed values (such as zero-element arrays) properly
 - I think this might be done but I'm not sure.

Not done, not even started.  See the referenced discussions, or the
limitations acknowledged here:
http://developer.postgresql.org/pgdocs/postgres/gin-limit.html

 Clean up VACUUM FULL's klugy transaction management
 - I think this is moot with the new cluster-based VF.

Right, that one's either done or no longer relevant depending on how you
want to look at it.


There is another TODO item that I was just struck by while reading your
response to Joseph Adams:

Fix system views like pg_stat_all_tables to use set-returning
functions, rather than views of per-column functions

What is the point of this proposal?  The reason for the per-column function
implementation was to allow people to slice and dice the underlying data
their own way.  Changing to monolithic SRFs would make that harder, and
I don't see that it's buying anything especially useful.  I'd vote for
taking this off unless someone can explain why it's an improvement.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Robert Haas]

On Fri, Mar 26, 2010 at 12:57 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 In reading through the TODO list, I noticed a few things that I think
 are done, may be done, or may be partially done.  See below.
 Thoughts?  ...Robert

 Add missing operators for geometric data types
 - this is at least partly done.  not sure if it is entirely done.

 I think you are thinking of Teodor's point_ops patch, but what that
 did was add GIST index support for some existing geometric operators.
 This TODO item suffers from the all-too-common sin of being uselessly
 vague; it doesn't identify what operators it thinks are missing.

Well, I'm thinking of that because that TODO item has a reference to
the point_ops patch.  I think we should remove this item and replace
it with any more specific suggestions someone cares to raise.

 Implement full support for window framing clauses.
 - Not sure if we made any progress on this or not.

 We made some progress for 9.0, but there's still a lot of unimplemented
 territory.

Perhaps it could be made more specific.

 Add UNIQUE capability to non-btree indexes
 - This is one application of exclusion constraints, so I'd say this is done.

 I think exclusion constraints address this as much as it needs to be
 addressed for GIST and GIN, neither of which have equality as a core
 concept.  Hash, however, does have that.  So I'd vote for narrowing the
 entry to support UNIQUE natively in hash indexes and moving it to the
 hash-index section.

As far as I know, exclusion constraints would work with hash opclasses
also.  Do you think there's an advantage to having something that is
hash-specific a la the btree-specific stuff we already have?

 [GIN] Support empty indexed values (such as zero-element arrays) properly
 - I think this might be done but I'm not sure.

 Not done, not even started.  See the referenced discussions, or the
 limitations acknowledged here:
 http://developer.postgresql.org/pgdocs/postgres/gin-limit.html

OK.

 Clean up VACUUM FULL's klugy transaction management
 - I think this is moot with the new cluster-based VF.

 Right, that one's either done or no longer relevant depending on how you
 want to look at it.

OK.

 There is another TODO item that I was just struck by while reading your
 response to Joseph Adams:

        Fix system views like pg_stat_all_tables to use set-returning
        functions, rather than views of per-column functions

 What is the point of this proposal?  The reason for the per-column function
 implementation was to allow people to slice and dice the underlying data
 their own way.  Changing to monolithic SRFs would make that harder, and
 I don't see that it's buying anything especially useful.  I'd vote for
 taking this off unless someone can explain why it's an improvement.

I assumed it would be faster and less likely to return inconsistent
results.  If that's not the case then I agree.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Tom Lane]

Robert Haas robertmh...@gmail.com writes:
 As far as I know, exclusion constraints would work with hash opclasses
 also.

Yeah, they do.

 Do you think there's an advantage to having something that is
 hash-specific a la the btree-specific stuff we already have?

Sure: it'll be more efficient because of not needing an AFTER trigger.
Now of course this assumes that hash indexes ever grow up enough to play
in the big leagues, which is a pretty dubious proposition; but having
real unique-constraint support is one of the things people would want
from them if they ever did get to be credible production choices.
So this isn't something I'd put at the front of the TODO list for hash
indexes, but it ought to be in there somewhere.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

[Bruce Momjian]

Thanks.  TODO updated.

 
 * Disallow LOCK on view
   change to
 * -Disallow LOCK on view (Mark H)
   well, at least when my patch is applied :)
 
 
 * Allow SQL function indexes
   This seems to work in the CVS code, or I have misunderstood:
   CREATE TABLE t ( a int);
   CREATE FUNCTION mod5(int) RETURNS int AS 'select $1 % 5' LANGUAGE 'sql';
   CREATE INDEX sql_index ON t ( mod5(a) );
 
 
 * Add ALTER TABLE command to change table ownership
   Dibs on this.
 
 
 -- 
 Mark Hollomon
 [EMAIL PROTECTED]
 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026