Re: First query very slow. Solutions: memory, or settings, or SQL?

On Sun, Jul 19, 2009 at 9:45 PM, Phoenix Kiula<phoenix(dot)kiula(at)gmail(dot)com> wrote:
> Hi.
>
> I have a query that should be very fast because it's using all
> indexes, but it takes a lot of time.
>
>
> explain analyze select * from sites where user_id = 'phoenix' order by
> id desc limit 10;
>
>                                                        QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=344.85..344.87 rows=10 width=262) (actual
> time=5879.069..5879.167 rows=10 loops=1)
>   ->  Sort  (cost=344.85..345.66 rows=323 width=262) (actual
> time=5879.060..5879.093 rows=10 loops=1)
>         Sort Key: id
>         ->  Index Scan using new_idx_sites_userid on sites
> (cost=0.00..331.39 rows=323 width=262) (actual time=44.408..5867.557
> rows=2178 loops=1)
>               Index Cond: ((user_id)::text = 'phoenix'::text)
>  Total runtime: 5879.414 ms
> (6 rows)
>
> Time: 5885.928 ms
>
>
> This query should really not be taking 6 seconds!
>
> So my theories:
>
> 1. Somehow the sorting is taking a lot of time. Even though it's on
> the primary key, to find "id desc" the query has to see all the rows.

Look again. The time is being taken up by the index scan. The index
scan is having to go through 2k+ entries before it finds the 10 you
want. Could be index bloat. Hard to say. Definitely faster once it's
all cached, but the first time it's hitting 2k index entries followed
by 2k table entries. Given that it takes 6 seconds to run, that's 333
disk entries scanned per second, not bad really.

> 2. Or the vacuums that I am doing are not very efficient? (Autovacuum
> is on, and there's nothing in the pg log!)

Try reindexing to see if that helps