Обсуждение: slow index lookup
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
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
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 >
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
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 > >
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
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 >
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
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 >
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