On Mon, Aug 15, 2011 at 7:37 PM, Greg Smith wrote:
> That's 5.4X as fast; not too shabby!
Awesome!
> And with the large difference in response time, things appear to be working
> as hoped even in this situation. If you try this on your laptop, where
> drive cache size and random I/O are likely
On 08/11/2011 04:06 PM, Robert Haas wrote:
On my laptop, the first query executes in about 555 ms, while the
second one takes about 1125 ms...I expect that you could get
an even larger benefit from this type of query if you could avoid
actual disk I/O, rather than just buffer cache thrashing, but
On Aug 13, 2011, at 4:31 PM, Heikki Linnakangas wrote:
>> The example is much more realistic if the query is a fetch of N latest rows
>> from a table. Very common use case, and the whole relation's visibility
>> statistics are completely wrong for that query.
>
> That is somewhat compensated by
On 13.08.2011 23:35, Kääriäinen Anssi wrote:
"""
Now, suppose that we know that 50% of the heap pages have their
visibility map bits set. What's the chance that this query won't need
a heap fetch? Well, the chance is 50% *if* you assume that a row
which has been quiescent for a long time is jus
"""
Now, suppose that we know that 50% of the heap pages have their
visibility map bits set. What's the chance that this query won't need
a heap fetch? Well, the chance is 50% *if* you assume that a row
which has been quiescent for a long time is just as likely to be
queried as one that has been
On Fri, Aug 12, 2011 at 5:39 PM, Kevin Grittner
wrote:
> Robert Haas wrote:
>
>> That's one of the points I asked for feedback on in my original
>> email. "How should the costing be done?"
>
> It seems pretty clear that there should be some cost adjustment. If
> you can't get good numbers someh
Robert Haas wrote:
> That's one of the points I asked for feedback on in my original
> email. "How should the costing be done?"
It seems pretty clear that there should be some cost adjustment. If
you can't get good numbers somehow on what fraction of the heap
accesses will be needed, I would
2011/8/12 PostgreSQL - Hans-Jürgen Schönig :
> is there any plan to revise the cost for index only scans compared to what it
> is now?
That's one of the points I asked for feedback on in my original email.
"How should the costing be done?"
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.c
On Aug 12, 2011, at 10:03 PM, Heikki Linnakangas wrote:
> On 11.08.2011 23:06, Robert Haas wrote:
>> Comments, testing, review appreciated...
>
> I would've expected this to use an index-only scan:
>
> postgres=# CREATE TABLE foo AS SELECT generate_series(1,10) AS id;
> SELECT 10
> postg
On Fri, Aug 12, 2011 at 4:03 PM, Heikki Linnakangas
wrote:
> On 11.08.2011 23:06, Robert Haas wrote:
>>
>> Comments, testing, review appreciated...
>
> I would've expected this to use an index-only scan:
>
> postgres=# CREATE TABLE foo AS SELECT generate_series(1,10) AS id;
Ugh. I think ther
On 11.08.2011 23:06, Robert Haas wrote:
Comments, testing, review appreciated...
I would've expected this to use an index-only scan:
postgres=# CREATE TABLE foo AS SELECT generate_series(1,10) AS id;
SELECT 10
postgres=# CREATE INDEX i_foo ON foo (id) WHERE id = 10;
CREATE INDEX
postgr
On Fri, Aug 12, 2011 at 9:31 AM, Cédric Villemain
wrote:
>> Well... PostgreSQL can only use the index on a or the index on b, not
>> both. This patch doesn't change that. I'm not trying to use indexes
>> in some completely new way; I'm just trying to make them faster by
>> optimizing away the he
2011/8/12 Robert Haas :
> On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain
> wrote:
Can this faux heap tuple be appended by the data from another index
once it has been created ? ( if the query involves those 2 index)
>>>
>>> I don't see how to make that work. In general, a query like
On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain
wrote:
>>> Can this faux heap tuple be appended by the data from another index
>>> once it has been created ? ( if the query involves those 2 index)
>>
>> I don't see how to make that work. In general, a query like "SELECT
>> a, b FROM foo WHERE a
Robert,
I imagine we store positional information in gin index and return
tuples in relevant order - instant full-text search !
Great work, guys !
Oleg
On Thu, 11 Aug 2011, Robert Haas wrote:
Please find attached a patch implementing a basic version of
index-only scans. This patch is the w
2011/8/12 Robert Haas :
> On Thu, Aug 11, 2011 at 5:39 PM, Cédric Villemain
> wrote:
>> 2011/8/11 Robert Haas :
>>> Please find attached a patch implementing a basic version of
>>> index-only scans. This patch is the work of my colleague Ibrar Ahmed
>>> and myself, and also incorporates some code
On Thu, Aug 11, 2011 at 9:44 PM, Greg Sabino Mullane wrote:
>>> Maybe it's time to finally remove the been-deprecated-for-a-while OIDs?
>
>> I thought about just not supporting that for index-only scans, but
>> system catalogs use them pretty extensively, and it doesn't seem out
>> of the question
On Thu, Aug 11, 2011 at 5:39 PM, Cédric Villemain
wrote:
> 2011/8/11 Robert Haas :
>> Please find attached a patch implementing a basic version of
>> index-only scans. This patch is the work of my colleague Ibrar Ahmed
>> and myself, and also incorporates some code from previous patches
>> posted
On 08/11/2011 09:44 PM, Greg Sabino Mullane wrote:
I guess the question that should be asked is "we are going to finally
remove OIDs someday, right?". If so, and if it's potentially blocking a
major new feature, why not now?
It seems a bit odd then that we added "ALTER TABLE SET WITH OIDS"
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
>> Maybe it's time to finally remove the been-deprecated-for-a-while OIDs?
> I thought about just not supporting that for index-only scans, but
> system catalogs use them pretty extensively, and it doesn't seem out
> of the question that that c
2011/8/11 Robert Haas :
> Please find attached a patch implementing a basic version of
> index-only scans. This patch is the work of my colleague Ibrar Ahmed
> and myself, and also incorporates some code from previous patches
> posted by Heikki Linnakanagas.
Great!.
>
> I'm able to demonstrate a
On Thu, Aug 11, 2011 at 4:57 PM, Greg Sabino Mullane wrote:
>> 1. The way that nodeIndexscan.c builds up the faux heap tuple is
>> perhaps susceptible to improvement. I thought about building a
>> virtual tuple, but then what do I do with an OID column, if I have
>> one? Or maybe this should be
On 08/11/2011 01:57 PM, Greg Sabino Mullane wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
1. The way that nodeIndexscan.c builds up the faux heap tuple is
perhaps susceptible to improvement. I thought about building a
virtual tuple, but then what do I do with an OID column, if I h
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> 1. The way that nodeIndexscan.c builds up the faux heap tuple is
> perhaps susceptible to improvement. I thought about building a
> virtual tuple, but then what do I do with an OID column, if I have
> one? Or maybe this should be done some o
On 31/05/10 18:09, Shrish Purohit wrote:
Sometime back you have started with "Separate Heap Fetch from Index Scan" which
was planned to support partial index only scans. Are you still working on it or do you
know someone still working on it?
I'm not working on it right now, but hopefully I'll
Hi Shrish,
I saw the excel that you sent to Heikki. Here are my comments.
1. Don't worry about comparing the results with Oracle. Just take pg-normal and
pg-enhanced. That's what the community cares about. Later we can also add
Oracle's performance.
2. In the excel sheet you have 'simple querie
Hi Heikki, Pgsql-Hackers,
Sometime back you have started with "Separate Heap Fetch from Index Scan" which
was planned to support partial index only scans. Are you still working on it or
do you know someone still working on it?
We did some development with Gokul's Index Only Patch and have got g
Hi Heikki,
I was recollecting our conversation that, if the index-only quals
were implemented through indexes by storing snapshots, broken data-types may
not be supported. I feel this problem might exist, if we go on design a
IOT(Index Organized Tables) / Clustered Indexes. IOT is again
In talking to developers, it appears that index-only scans will not be
implemented in Postgres 8.5 due to development time being spend on hot
standby and streaming replication. The current index-only scan status
is summarized here:
http://archives.postgresql.org/pgsql-hackers/2009-07/msg0
[ thinks a bit ... ] At least for GIST, it is possible that whether
data can be regurgitated will vary depending on the selected opclass.
Some opclasses use the STORAGE modifier and some don't. I am not sure
how hard we want to work to support flexibility there. Would it be
sufficient to hard-c
On Mon, Jul 13, 2009 at 11:32 AM, Heikki
Linnakangas wrote:
> Tom Lane wrote:
>> One thought here is that an AM call isn't really free, and doing two of
>> them instead of one mightn't be such a good idea. I would suggest
>> either having a separate AM entry point to get both bits of data
>> ("amg
On Wed, Jul 15, 2009 at 1:21 AM, Ron Mayer wrote:
> Really? I'd have thought that index is similar to materializing
> these views:
> create view a_b as select aid,bid from manytomany order by aid,bid;
> create view b_a as select bid,aid from manytomany order by bid,aid;
> Or perhaps
> create vi
Heikki Linnakangas wrote:
> ...
> CREATE TABLE manytomany (aid integer, bid integer);
> CREATE INDEX a_b ON manytomany (aid, bid);
> CREATE INDEX b_a ON manytomany (bid, aid);
> ...
>> new and interesting indexing strategies. Covered indexes are also one
>> kind of materialized view. It may be bett
Now I'm back where I can go look at the source code :-)
Thanks.
> -Original Message-
> From: Jaime Casanova [mailto:jcasa...@systemguards.com.ec]
> Sent: Monday, July 13, 2009 8:40 PM
> To: Mischa Sandberg
> Cc: Heikki Linnakangas; PostgreSQL-development
> Subje
On Monday 13 July 2009 16:38:18 Bruce Momjian wrote:
> Heikki Linnakangas wrote:
> > Even if we don't solve the visibility
> > map problem, just allowing the executor to evaluate quals that are not
> > directly indexable using data from the index, would be useful. For
> > example, "SELECT * FROM fo
On Tue, 2009-07-14 at 11:23 +0300, Heikki Linnakangas wrote:
> > The single SQL example mentioned already has at least two mechanisms for
> > improving performance of that type of query. We probably don't need
> > another, or at least we need a good analysis of why.
>
> Well, another class of qu
Simon Riggs wrote:
> On Mon, 2009-07-13 at 10:16 +0300, Heikki Linnakangas wrote:
>
>> Implementing index-only scans requires a few changes:
>
> I would like to see a clear exposition of the use cases and an an
> analysis of the costs and benefits of doing this. It sounds cool, but I
> want to kn
Simon Riggs wrote:
> On Mon, 2009-07-13 at 10:16 +0300, Heikki Linnakangas wrote:
>
>> Implementing index-only scans requires a few changes:
>
> I would like to see a clear exposition of the use cases and an an
> analysis of the costs and benefits of doing this. It sounds cool, but I
> want to kn
On Mon, Jul 13, 2009 at 5:38 PM, Mischa
Sandberg wrote:
> Does PG have an intermediate execution node to sort/batch index entries (heap
> tuple ptrs) by heap page prior to lookup? Something mssql does ...
>
it sounds a lot like a bitmap index scan
--
Atentamente,
Jaime Casanova
Soporte y capac
an
> Sent: Monday, July 13, 2009 6:38 AM
> To: Heikki Linnakangas
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] Index-only scans
>
> Heikki Linnakangas wrote:
> > Even if we don't solve the visibility
> > map problem, just allowing the executor to evaluate
Greg Stark wrote:
> On Mon, Jul 13, 2009 at 7:04 PM, Kevin
> Grittner wrote:
>> As far as our production queries go, based on our experience with
>> several other products against this schema, this is the area where
>> the biggest performance gains remain to be realized.
>
>
> There's a logical
On Mon, Jul 13, 2009 at 7:04 PM, Kevin
Grittner wrote:
> As far as our production queries go, based on our experience with
> several other products against this schema, this is the area where the
> biggest performance gains remain to be realized.
There's a logical fallacy implicit in this stateme
On Mon, 2009-07-13 at 10:16 +0300, Heikki Linnakangas wrote:
> Implementing index-only scans requires a few changes:
I would like to see a clear exposition of the use cases and an an
analysis of the costs and benefits of doing this. It sounds cool, but I
want to know it is cool before we spend t
Heikki Linnakangas wrote:
> Implementing index-only scans requires a few changes:
I'm happy to see this work! Now that the EXISTS predicates have been
optimized to consider semi-join and anti-join techniques, I believe
that these index issues (evaluating quals before heap access and
skipping he
Tom Lane wrote:
> One thought here is that an AM call isn't really free, and doing two of
> them instead of one mightn't be such a good idea. I would suggest
> either having a separate AM entry point to get both bits of data
> ("amgettupledata"?) or adding an optional parameter to amgettuple.
I'm
Heikki Linnakangas writes:
> Tom Lane wrote:
>> What are you going to do for index types that don't store the original
>> data (e.g. hash)?
> They will obviously not be able to regurgitate index tuples. I have not
> yet decided how that's going to be signaled.
Well, I think that's a pretty criti
Tom Lane wrote:
> Heikki Linnakangas writes:
>> At the moment, amgettuple only returns pointers to heap tuples. There is
>> no way to return data from the index tuples. That needs to be changed to
>> support index-only scans.
>
> What are you going to do for index types that don't store the origi
Heikki Linnakangas writes:
> At the moment, amgettuple only returns pointers to heap tuples. There is
> no way to return data from the index tuples. That needs to be changed to
> support index-only scans.
What are you going to do for index types that don't store the original
data (e.g. hash)?
Heikki Linnakangas wrote:
> Even if we don't solve the visibility
> map problem, just allowing the executor to evaluate quals that are not
> directly indexable using data from the index, would be useful. For
> example, "SELECT * FROM foo WHERE textcol LIKE '%bar%', and you have a
> b-tree index on
On Mon, Jul 13, 2009 at 8:19 AM, Heikki
Linnakangas wrote:
>
> I propose that we split index_getnext into two steps: fetching the next
> match from the index (index_next()), and fetching the corresponding heap
> tuple (index_fetch()).
A pretty trivial concern, but it seems confusing that the funct
At the moment, amgettuple only returns pointers to heap tuples. There is
no way to return data from the index tuples. That needs to be changed to
support index-only scans.
I propose that we split index_getnext into two steps: fetching the next
match from the index (index_next()), and fetching the
Implementing index-only scans requires a few changes:
1. indexam API changes
There's currently no way to return data from an index scan. You only get
TID pointers to heap tuples.
2. Make visibility map crash-safe
After crash, the visibility map can currently be left in state where it
has some b
101 - 152 of 152 matches
Mail list logo
