pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow index lookup
9+ messages / 5 participants
[nested] [flat]
* slow index lookup
@ 2010-06-22 21:44 Anj Adu <fotographs@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Anj Adu @ 2010-06-22 21:44 UTC (permalink / raw)
To: pgsql-performance
This query seems unreasonable slow on a well-indexed table (13 million
rows). Separate indexes are present on guardid_id , from_num and
targetprt columns.
The table was analyzed with a default stats target of 600.
Postgres 8.1.9 on 2 cpu quad core 5430 with 32G RAM (work_mem=502400)
6 x 450G 15K disks on a RAID 10 setup. (RHEL 5 )
The table size is 3.6GB (table + indexes)
explain analyze select 1 from mydev_tr_hr_dimension_2010_06_13 where
guardid_id=19 and from_num=184091764 and targetprt=25 limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..323.36 rows=1 width=0) (actual
time=19238.104..19238.104 rows=0 loops=1)
-> Index Scan using mydev_tr_hr_dimension_2010_06_13_from_num on
mydev_tr_hr_dimension_2010_06_13 (cost=0.00..26515.46 rows=82
width=0) (actual time=19238.103..19238.103 rows=0 loops=1)
Index Cond: (from_num = 184091764)
Filter: ((guardid_id = 19) AND (targetprt = 25))
Total runtime: 19238.126 ms
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: slow index lookup
@ 2010-06-22 23:44 Alvaro Herrera <alvherre@commandprompt.com>
parent: Anj Adu <fotographs@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Alvaro Herrera @ 2010-06-22 23:44 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: pgsql-performance
Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400 2010:
> This query seems unreasonable slow on a well-indexed table (13 million
> rows). Separate indexes are present on guardid_id , from_num and
> targetprt columns.
Maybe you need to vacuum or reindex?
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: slow index lookup
@ 2010-06-23 01:00 Anj Adu <fotographs@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 9+ messages in thread
From: Anj Adu @ 2010-06-23 01:00 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-performance
i have several partitions like this (similar size ...similar data
distribution)..these partitions are only "inserted"..never updated.
Why would I need to vacuum..
I can reindex..just curious what can cause the index to go out of whack.
On Tue, Jun 22, 2010 at 4:44 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400 2010:
>> This query seems unreasonable slow on a well-indexed table (13 million
>> rows). Separate indexes are present on guardid_id , from_num and
>> targetprt columns.
>
> Maybe you need to vacuum or reindex?
>
> --
> Álvaro Herrera <alvherre@commandprompt.com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: slow index lookup
@ 2010-06-23 01:10 Joshua D. Drake <jd@commandprompt.com>
parent: Anj Adu <fotographs@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Joshua D. Drake @ 2010-06-23 01:10 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
On Tue, 2010-06-22 at 18:00 -0700, Anj Adu wrote:
> i have several partitions like this (similar size ...similar data
> distribution)..these partitions are only "inserted"..never updated.
> Why would I need to vacuum..
>
An explain analyze is what is in order for further diagnosis.
JD
> I can reindex..just curious what can cause the index to go out of whack.
>
> On Tue, Jun 22, 2010 at 4:44 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
> > Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400 2010:
> >> This query seems unreasonable slow on a well-indexed table (13 million
> >> rows). Separate indexes are present on guardid_id , from_num and
> >> targetprt columns.
> >
> > Maybe you need to vacuum or reindex?
> >
> > --
> > Álvaro Herrera <alvherre@commandprompt.com>
> > The PostgreSQL Company - Command Prompt, Inc.
> > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> >
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: slow index lookup
@ 2010-06-23 01:21 Anj Adu <fotographs@gmail.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 9+ messages in thread
From: Anj Adu @ 2010-06-23 01:21 UTC (permalink / raw)
To: jd@commandprompt.com; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
I did post the explain analyze..can you please clarify
On Tue, Jun 22, 2010 at 6:10 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> On Tue, 2010-06-22 at 18:00 -0700, Anj Adu wrote:
>> i have several partitions like this (similar size ...similar data
>> distribution)..these partitions are only "inserted"..never updated.
>> Why would I need to vacuum..
>>
>
> An explain analyze is what is in order for further diagnosis.
>
> JD
>
>
>> I can reindex..just curious what can cause the index to go out of whack.
>>
>> On Tue, Jun 22, 2010 at 4:44 PM, Alvaro Herrera
>> <alvherre@commandprompt.com> wrote:
>> > Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400 2010:
>> >> This query seems unreasonable slow on a well-indexed table (13 million
>> >> rows). Separate indexes are present on guardid_id , from_num and
>> >> targetprt columns.
>> >
>> > Maybe you need to vacuum or reindex?
>> >
>> > --
>> > Álvaro Herrera <alvherre@commandprompt.com>
>> > The PostgreSQL Company - Command Prompt, Inc.
>> > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>> >
>>
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: slow index lookup
@ 2010-06-23 02:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 2 replies; 9+ messages in thread
From: Tom Lane @ 2010-06-23 02:01 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Anj Adu <fotographs@gmail.com>; pgsql-performance
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400 2010:
>> This query seems unreasonable slow on a well-indexed table (13 million
>> rows). Separate indexes are present on guardid_id , from_num and
>> targetprt columns.
> Maybe you need to vacuum or reindex?
Rethinking the set of indexes is probably a more appropriate suggestion.
Separate indexes aren't usefully combinable for a case like this --- in
principle the thing could do a BitmapAnd, but the startup time would be
pretty horrid, and the LIMIT 1 is discouraging it from trying that.
If this is an important case to optimize then you need a 3-column index.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: slow index lookup
@ 2010-06-23 03:05 Anj Adu <fotographs@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Anj Adu @ 2010-06-23 03:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
Appears to have helped with the combination index. I'll need to
eliminate caching effects before making sure its the right choice.
Thanks for the suggestion.
On Tue, Jun 22, 2010 at 7:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400 2010:
>>> This query seems unreasonable slow on a well-indexed table (13 million
>>> rows). Separate indexes are present on guardid_id , from_num and
>>> targetprt columns.
>
>> Maybe you need to vacuum or reindex?
>
> Rethinking the set of indexes is probably a more appropriate suggestion.
> Separate indexes aren't usefully combinable for a case like this --- in
> principle the thing could do a BitmapAnd, but the startup time would be
> pretty horrid, and the LIMIT 1 is discouraging it from trying that.
> If this is an important case to optimize then you need a 3-column index.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: slow index lookup
@ 2010-06-23 16:59 Anj Adu <fotographs@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Anj Adu @ 2010-06-23 16:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
The combination index works great. Would adding the combination index
guarantee that the optimizer will choose that index for these kind of
queries involving the columns in the combination. I verified a couple
of times and it picked the right index. Just wanted to make sure it
does that consistently.
On Tue, Jun 22, 2010 at 7:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400 2010:
>>> This query seems unreasonable slow on a well-indexed table (13 million
>>> rows). Separate indexes are present on guardid_id , from_num and
>>> targetprt columns.
>
>> Maybe you need to vacuum or reindex?
>
> Rethinking the set of indexes is probably a more appropriate suggestion.
> Separate indexes aren't usefully combinable for a case like this --- in
> principle the thing could do a BitmapAnd, but the startup time would be
> pretty horrid, and the LIMIT 1 is discouraging it from trying that.
> If this is an important case to optimize then you need a 3-column index.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: slow index lookup
@ 2010-06-23 17:08 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Anj Adu <fotographs@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Kevin Grittner @ 2010-06-23 17:08 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
Anj Adu <fotographs@gmail.com> wrote:
> The combination index works great. Would adding the combination
> index guarantee that the optimizer will choose that index for
> these kind of queries involving the columns in the combination. I
> verified a couple of times and it picked the right index. Just
> wanted to make sure it does that consistently.
It's cost based -- as long as it thinks that approach will be
faster, it will use it.
-Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-06-23 17:08 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-22 21:44 slow index lookup Anj Adu <fotographs@gmail.com>
2010-06-22 23:44 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-06-23 01:00 ` Anj Adu <fotographs@gmail.com>
2010-06-23 01:10 ` Joshua D. Drake <jd@commandprompt.com>
2010-06-23 01:21 ` Anj Adu <fotographs@gmail.com>
2010-06-23 02:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-23 03:05 ` Anj Adu <fotographs@gmail.com>
2010-06-23 16:59 ` Anj Adu <fotographs@gmail.com>
2010-06-23 17:08 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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