pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery about index usage
8+ messages / 4 participants
[nested] [flat]
* Query about index usage
@ 2010-06-11 09:56 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 3 replies; 8+ messages in thread
From: Jayadevan M @ 2010-06-11 09:56 UTC (permalink / raw)
To: pgsql-performance
Hello all,
One query about PostgreSQL's index usage. If I select just one column on
which there is an index (or select only columns on which there is an
index), and the index is used by PostgreSQL, does PostgreSQL avoid table
access if possible? I am trying to understand the differences between
Oracle's data access patterns and PostgreSQL's.
Here is how it works in Oracle.
Case 1 - SELECT column which is not there in the index
SQL> select name from myt where id = 13890;
NAME
---------------------------------------------------------------------------------------------------
AAAA
Execution Plan
----------------------------------------------------------
Plan hash value: 2609414407
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYT | 1 | 65 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | MYIDX | 1 | | 1 (0)|
00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=13890)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Case 1 - SELECT column which is there in the index
SQL> select id from myt where id = 13890;
ID
----------
13890
Execution Plan
----------------------------------------------------------
Plan hash value: 2555454399
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| MYIDX | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=13890)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
In the second query where id was selected, the table was not used at all.
In PosgreSQL, explain gives me similar output in both cases.
Table structure -
postgres=# \d myt
Table "public.myt"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
Indexes:
"myidx" btree (id)
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query about index usage
@ 2010-06-11 15:25 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2 siblings, 2 replies; 8+ messages in thread
From: Kevin Grittner @ 2010-06-11 15:25 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; pgsql-performance
Jayadevan M <Jayadevan.Maymala@ibsplc.com> wrote:
> One query about PostgreSQL's index usage. If I select just one
> column on which there is an index (or select only columns on which
> there is an index), and the index is used by PostgreSQL, does
> PostgreSQL avoid table access if possible?
PostgreSQL can't currently avoid reading the table, because that's
where the tuple visibility information is stored. We've been making
progress toward having some way to avoid reading the table for all
except very recently written tuples, but we're not there yet (in any
production version or in the 9.0 version to be released this
summer).
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query about index usage
@ 2010-06-11 15:32 Greg Smith <greg@2ndquadrant.com>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2 siblings, 0 replies; 8+ messages in thread
From: Greg Smith @ 2010-06-11 15:32 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-performance
Jayadevan M wrote:
> One query about PostgreSQL's index usage. If I select just one column on
> which there is an index (or select only columns on which there is an
> index), and the index is used by PostgreSQL, does PostgreSQL avoid table
> access if possible?
PostgreSQL keeps information about what rows are visible or not in with
the row data. It's therefore impossible at this time for it to answer
queries just based on what's in an index. Once candidate rows are found
using one, the database must then also retrieve the row(s) and do a
second check as to whether it's visible to the running transaction or
not before returning them to the client.
Improving this situation is high up on the list of things to improve in
PostgreSQL and the value of it recognized, it just hasn't been built yet.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query about index usage
@ 2010-06-11 18:50 Bob Lunney <bob_lunney@yahoo.com>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2 siblings, 0 replies; 8+ messages in thread
From: Bob Lunney @ 2010-06-11 18:50 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, Jayadevan M <Jayadevan.Maymala@ibsplc.com>
Jayadevan,
PostgreSQL must go to the table to determine if the row you are requesting is visible to your transaction. This is an artifact of the MVCC implementation. Oracle can fetch the data from the index, since it doesn't keep multiple representations of the rows, but it may need to check the undo logs to determine the state that applies to your transaction. Its just two different ways to accomplish the same thing.
Bob Lunney
--- On Fri, 6/11/10, Jayadevan M <Jayadevan.Maymala@ibsplc.com> wrote:
> From: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
> Subject: [PERFORM] Query about index usage
> To: pgsql-performance@postgresql.org
> Date: Friday, June 11, 2010, 5:56 AM
> Hello all,
>
> One query about PostgreSQL's index usage. If I select just
> one column on
> which there is an index (or select only columns on which
> there is an
> index), and the index is used by PostgreSQL, does
> PostgreSQL avoid table
> access if possible? I am trying to understand the
> differences between
> Oracle's data access patterns and PostgreSQL's.
> Here is how it works in Oracle.
>
> Case 1 - SELECT column which is not there in the index
>
> SQL> select name from myt where id = 13890;
>
> NAME
> ---------------------------------------------------------------------------------------------------
> AAAA
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 2609414407
>
> -------------------------------------------------------------------------------------
> | Id | Operation
> | Name |
> Rows | Bytes | Cost (%CPU)|
> Time |
> -------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT
> |
> | 1 |
> 65 | 2 (0)|
> 00:00:01 |
> | 1 | TABLE ACCESS BY INDEX ROWID|
> MYT | 1 |
> 65 | 2 (0)|
>
> 00:00:01 |
> |* 2 | INDEX RANGE SCAN
> | MYIDX | 1
> | |
> 1 (0)|
> 00:00:01 |
> -------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("ID"=13890)
>
> Note
> -----
> - dynamic sampling used for this
> statement
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 4 consistent gets
> 0 physical reads
> 0 redo size
> 409 bytes sent via
> SQL*Net to client
> 384 bytes received via
> SQL*Net from client
> 2 SQL*Net
> roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
>
> Case 1 - SELECT column which is there in the index
>
> SQL> select id from myt where id = 13890;
>
> ID
> ----------
> 13890
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 2555454399
>
> --------------------------------------------------------------------------
> | Id | Operation |
> Name | Rows | Bytes | Cost (%CPU)| Time
> |
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT |
> | 1 |
> 13 | 1 (0)|
> 00:00:01 |
> |* 1 | INDEX RANGE SCAN| MYIDX |
> 1 | 13 |
> 1 (0)| 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - access("ID"=13890)
>
> Note
> -----
> - dynamic sampling used for this
> statement
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3 consistent gets
> 0 physical reads
> 0 redo size
> 407 bytes sent via
> SQL*Net to client
> 384 bytes received via
> SQL*Net from client
> 2 SQL*Net
> roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> In the second query where id was selected, the table was
> not used at all.
> In PosgreSQL, explain gives me similar output in both
> cases.
> Table structure -
>
> postgres=# \d myt
> Table
> "public.myt"
> Column | Type
> | Modifiers
> --------+-----------------------+-----------
> id | integer
> |
> name | character varying(20) |
> Indexes:
> "myidx" btree (id)
>
>
> Regards,
> Jayadevan
>
>
>
>
>
> DISCLAIMER:
>
> "The information in this e-mail and any attachment is
> intended only for
> the person to whom it is addressed and may contain
> confidential and/or
> privileged material. If you have received this e-mail in
> error, kindly
> contact the sender and destroy all copies of the original
> communication.
> IBS makes no warranty, express or implied, nor guarantees
> the accuracy,
> adequacy or completeness of the information contained in
> this email or any
> attachment and is not liable for any errors, defects,
> omissions, viruses
> or for resultant loss or damage, if any, direct or
> indirect."
>
>
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query about index usage
@ 2010-06-14 03:40 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 8+ messages in thread
From: Jayadevan M @ 2010-06-14 03:40 UTC (permalink / raw)
To: pgsql-performance
> PostgreSQL can't currently avoid reading the table, because that's
> where the tuple visibility information is stored. We've been making
> progress toward having some way to avoid reading the table for all
> except very recently written tuples, but we're not there yet (in any
> production version or in the 9.0 version to be released this
> summer).
Thank you for all the replies. I am learning PostgreSQL and figuring out
which of the standard techniques for tuning queries in Oracle works in
PostgreSQL as well. Thank you.
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query about index usage
@ 2010-06-23 04:16 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 8+ messages in thread
From: Jayadevan M @ 2010-06-23 04:16 UTC (permalink / raw)
To: pgsql-performance
Hello,
> PostgreSQL can't currently avoid reading the table, because that's
> where the tuple visibility information is stored. We've been making
> progress toward having some way to avoid reading the table for all
> except very recently written tuples, but we're not there yet (in any
> production version or in the 9.0 version to be released this
> summer).
More doubts on how indexes are used by PostgreSQL. It is mentioned that
table data blocks have data about tuple visibility and hence table scans
are always necessary. So how does PostgreSQL reduce the number of blocks
to be read by using indexes? Does this mean that indexes will have
references to all the 'possible' blocks which may contain the data one is
searching for, and then scans all those blocks and eliminates records
which should not be 'visible' to the query being executed? Do index data
get updated as and when data is committed and made 'visible' or is it
that index data get updated as soon as data is changed, before commit is
issued and rollback of transaction results in a rollback of the index data
changes too?
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query about index usage
@ 2010-06-23 06:05 Greg Smith <greg@2ndquadrant.com>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 1 reply; 8+ messages in thread
From: Greg Smith @ 2010-06-23 06:05 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-performance
Jayadevan M wrote:
> It is mentioned that table data blocks have data about tuple visibility and hence table scans
> are always necessary. So how does PostgreSQL reduce the number of blocks
> to be read by using indexes?
To be useful, a query utilizing an index must be selective: it must
only return a fraction of the possible rows in the table. Scanning the
index will produce a list of blocks that contain the potentially visible
data, then only those data blocks will be retrieved and tested for
visibility.
Let's say you have a table that's 100 pages (pages are 8KB) and an index
that's 50 pages against it. You run a query that only selects 5% of the
rows in the table, from a continuous section. Very rough estimate, it
will look at 5% * 50 = 3 index pages. Those will point to a matching
set of 5% * 100 = 5 data pages. Now you've just found the right subset
of the data by only retrieving 8 random pages of data instead of 100.
With random_page_cost=4.0, that would give this plan a cost of around
32, while the sequential scan one would cost 100 * 1.0 (sequential
accesses) for a cost of around 100 (Both of them would also have some
smaller row processing cost added in there too).
It's actually a bit more complicated than that--the way indexes are
built means you can't just linearly estimate their usage, and scans of
non-contiguous sections are harder to model simply--but that should give
you an idea. Only when using the index significantly narrows the number
of data pages expected will it be an improvement over ignoring the index
and just scanning the whole table.
If the expected use of the index was only 20% selective for another
query, you'd be getting 20% * 50 = 10 index pages, 20% * 100 = 20 data
pages, for a potential total of 30 random page lookups. That could end
up costing 30 * 4.0 = 120, higher than the sequential scan. Usually
the breakpoint for how much of a table has to be scanned before just
scanning the whole thing sequentially is considered cheaper happens near
20% of it, and you can shift it around by adjusting random_page_cost.
Make it lower, and you can end up preferring index scans even for 30 or
40% of a table.
> Do index data get updated as and when data is committed and made 'visible' or is it
> that index data get updated as soon as data is changed, before commit is
> issued and rollback of transaction results in a rollback of the index data
Index changes happen when the data goes into the table, including
situations where it might not be committed. The index change doesn't
ever get deferred to commit time, like you can things like foreign key
checks. When a transaction is rolled back, the aborted row eventually
gets marked as dead by vacuum, at which point any index records pointing
to it can also be cleaned up.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query about index usage
@ 2010-06-23 06:15 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jayadevan M @ 2010-06-23 06:15 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: pgsql-performance
Thank you for the detailed explanation.
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-06-23 06:15 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-11 09:56 Query about index usage Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-06-11 15:25 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-14 03:40 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-06-23 04:16 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-06-23 06:05 ` Greg Smith <greg@2ndquadrant.com>
2010-06-23 06:15 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-06-11 15:32 ` Greg Smith <greg@2ndquadrant.com>
2010-06-11 18:50 ` Bob Lunney <bob_lunney@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox