Re: Slow indexscan - Mailing list pgsql-performance
From | Joshua D. Drake |
---|---|
Subject | Re: Slow indexscan |
Date | |
Msg-id | 46794F73.5010201@commandprompt.com Whole thread Raw |
In response to | Re: Slow indexscan ("Mikko Partio" <mpartio@gmail.com>) |
List | pgsql-performance |
Mikko Partio wrote: > > > On 6/20/07, *Tom Lane* <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote: > > "Mikko Partio" <mpartio@gmail.com <mailto:mpartio@gmail.com>> writes: > > > Index Scan using tbl_20070601_pkey on tbl_20070601 t1 > > (cost=0.00..365.13rows=13 width=137) (actual time= > > 120.83..10752.64 rows=539 loops=1) > > Index Cond: ((validtime >= 20070602000000::bigint) AND > (validtime <= > > 20070602235500::bigint) AND (latitude = 60.2744::double > precision) AND > > (longitude = 26.4417::double precision)) > > Filter: (parname = 'temperature'::character varying) > > You do realize that's going to scan the entire index range from > 20070602000000 to 20070602235500? > > If this is a typical query you'd be better off putting the lat/long > columns first in the index. > > regards, tom lane > > > > Thanks for the reply. > > Adding a new index does not speed up the query (although the planner > decides to use the index): > > db=# create index tbl_20070601_latlonvalidpar_index on tbl_20070601 > (latitude,longitude,validtime,parname); > CREATE INDEX > > db=# explain analyze select * from tbl_20070601 where validtime between > 20070602000000 and 20070602235500 and latitude= 60.2744 and > longitude=26.4417 and parname in ('temperature'); > > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > Index Scan using tbl_20070601_latlonvalidpar_index on tbl_20070601 t1 > (cost=0.00..29.18 rows=13 width=137) (actual time=3471.94..31542.90 > rows=539 loops=1) > Index Cond: ((latitude = 60.2744::double precision) AND (longitude = > 26.4417::double precision) AND (validtime >= 20070602000000::bigint) AND > (validtime <= 20070602235500::bigint) AND (parname = > 'temperature'::character varying)) > Total runtime: 31544.48 msec > (3 rows) > > > This is a very typical query and therefore it should be made as fast as > possible. There are several tables like this rowcount ranging from 3 > million to 13 million. I have some possibilities to modify the queries > as well as the tables, but the actual table structure is hard coded. > > Any other suggestions? Try increasing your default_statistics_target and rerunning explain analyze. Secondly try increasing your work_mem. Joshua D. Drake > > Regards > > MP > > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
pgsql-performance by date: