Re: Slow query - possible bug?

Gavin Hamill wrote:
> chris smith wrote:
>
>> 1.6secs isn't too bad on 4.3mill rows...
>>
>> How many entries are there for that date range?
>>
>>
> 1.7 secs /is/ good - it typically takes 5 or 6 seconds, which isn't so
> good. My question is 'why does the planner choose such a bizarre range
> request when both elements of the 'between' are identical? :)'

What's bizarre about the range request, and are you sure it's searching
doing the union of both conditions separately? It looks to me like it's
doing a standard range-search. If it was trying to fetch 4.3 million
rows via that index, I'd expect it to use a different index instead.

If you've got stats turned on, look in pg_stat_user_indexes/tables
before and after the query to see. Here's an example of a similar query
against one of my log tables. It's small, but the clause is the same,
and I don't see any evidence of the whole table being selected.

lamp=> SELECT * FROM pg_stat_user_indexes WHERE relname LIKE 'act%';
relid | indexrelid | schemaname | relname | indexrelname |
idx_scan | idx_tup_read | idx_tup_fetch
---------+------------+------------+---------+----------------+----------+--------------+---------------
6124993 | 7519044 | public | act_log | act_log_ts_idx |
23 | 18 | 18
6124993 | 7371115 | public | act_log | act_log_pkey |
0 | 0 | 0
(2 rows)

lamp=> EXPLAIN ANALYSE SELECT * FROM act_log WHERE al_ts BETWEEN
'2006-04-05 14:10:23+00'::timestamptz AND '2006-04-05
14:10:23+00'::timestamptz;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using act_log_ts_idx on act_log (cost=0.00..3.02 rows=1
width=102) (actual time=0.116..0.131 rows=1 loops=1)
Index Cond: ((al_ts >= '2006-04-05 15:10:23+01'::timestamp with time
zone) AND (al_ts <= '2006-04-05 15:10:23+01'::timestamp with time zone))
Total runtime: 0.443 ms
(3 rows)

lamp=> SELECT * FROM pg_stat_user_indexes WHERE relname LIKE 'act%';
relid | indexrelid | schemaname | relname | indexrelname |
idx_scan | idx_tup_read | idx_tup_fetch
---------+------------+------------+---------+----------------+----------+--------------+---------------
6124993 | 7519044 | public | act_log | act_log_ts_idx |
24 | 19 | 19
6124993 | 7371115 | public | act_log | act_log_pkey |
0 | 0 | 0
(2 rows)

1. vacuum full verbose your table (and post the output please)
2. perhaps reindex?
3. Try the explain analyse again and see what happens.
--
Richard Huxton
Archonet Ltd