Re: Slow query performance on large table
От | Tomasz Myrta |
---|---|
Тема | Re: Slow query performance on large table |
Дата | |
Msg-id | 3E64E27A.3050708@klaster.net обсуждение исходный текст |
Ответ на | Re: Slow query performance on large table ("Paul McKay" <paul_mckay@clearwater-it.co.uk>) |
Список | pgsql-performance |
Robert Treat wrote: > Maybe it's just me, but I get the feeling you need to work some regular > reindexing into your maintenance schedule. Given your query is using > between, I don't think it would use the index on the time field anyway > (and explain analyze seems to be supporting this). Rewrite it so that > you have a and time > foo and time < bar and I think you'll see a > difference. With that in mind, I think your speedier query results are > due more to having a non-bloated index freshly created than the fact > that it being a dual column index. > > Robert Treat Do you know anything about between, what should we know? I made some tests, and there was no noticable difference between them: pvwatch=# EXPLAIN analyze * from stats where hostid=1 and stp between 1 and 2; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using ind_stats on stats (cost=0.00..6.01 rows=1 width=28) (actual time=0.00..0.00 rows=0 loops=1) Index Cond: ((hostid = 1) AND (stp >= 1) AND (stp <= 2)) Total runtime: 0.00 msec (3 rows) pvwatch=# EXPLAIN analyze SELECT * from stats where hostid=1 and stp> 1 and stp<2; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using ind_stats on stats (cost=0.00..6.01 rows=1 width=28) (actual time=0.00..0.00 rows=0 loops=1) Index Cond: ((hostid = 1) AND (stp > 1) AND (stp < 2)) Total runtime: 0.00 msec (3 rows) Regards, Tomasz Myrta
В списке pgsql-performance по дате отправления: