Re: Slow query performance on large table

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