Thread: Index Backward Scan fast / Index Scan slow !

Hi,

I test many times the foolowing query.

dps=# explain analyze select next_index_time from url order by
next_index_time desc limit 1;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
----
  Limit  (cost=0.00..2.62 rows=1 width=4) (actual time=56.615..56.616
rows=1 loops=1)
    ->  Index Scan Backward using url_next_index_time on url
(cost=0.00..768529.55 rows=293588 width=4) (actual time=56.610..56.610
rows=1 loops=1)
  Total runtime: 56.669 ms
(3 rows)

dps=# explain analyze select next_index_time from url order by
next_index_time asc limit 1;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-
  Limit  (cost=0.00..2.62 rows=1 width=4) (actual
time=94879.636..94879.637 rows=1 loops=1)
    ->  Index Scan using url_next_index_time on url
(cost=0.00..768529.55 rows=293588 width=4) (actual
time=94879.631..94879.631 rows=1 loops=1)
  Total runtime: 94879.688 ms
(3 rows)

How to optimize the last query ? (~ 2000 times slower than the first
one)
I suppose there is some odd distribution of data in the index ?
Is the solution to reindex data ?

Cordialement,
Jean-Gérard Pailloncy

=?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= <pailloncy@ifrance.com> writes:
> How to optimize the last query ? (~ 2000 times slower than the first
> one)
> I suppose there is some odd distribution of data in the index ?

Looks to me like a whole lot of dead rows at the left end of the index.
Have you VACUUMed this table lately?  It would be interesting to see
what VACUUM VERBOSE has to say about it.

> Is the solution to reindex data ?

In 7.4 a VACUUM should be sufficient ... or at least, if it isn't
I'd like to know why not before you destroy the evidence by reindexing.

            regards, tom lane

Hi,

>> How to optimize the last query ? (~ 2000 times slower than the first
>> one)
>> I suppose there is some odd distribution of data in the index ?
>
> Looks to me like a whole lot of dead rows at the left end of the index.
> Have you VACUUMed this table lately?
 From pg_autovacuum:
[2004-04-10 05:45:39 AM] Performing: ANALYZE "public"."url"
[2004-04-10 11:13:25 AM] Performing: ANALYZE "public"."url"
[2004-04-10 03:12:14 PM] Performing: VACUUM ANALYZE "public"."url"
[2004-04-11 04:58:29 AM] Performing: ANALYZE "public"."url"
[2004-04-11 03:48:25 PM] Performing: ANALYZE "public"."url"
[2004-04-11 09:21:31 PM] Performing: ANALYZE "public"."url"
[2004-04-12 03:24:06 AM] Performing: ANALYZE "public"."url"
[2004-04-12 07:20:08 AM] Performing: VACUUM ANALYZE "public"."url"

>  It would be interesting to see
> what VACUUM VERBOSE has to say about it.
dps=# VACUUM VERBOSE url;
INFO:  vacuuming "public.url"
INFO:  index "url_pkey" now contains 348972 row versions in 2344 pages
DETAIL:  229515 index row versions were removed.
41 index pages have been deleted, 41 are currently reusable.
CPU 0.32s/1.40u sec elapsed 70.66 sec.
INFO:  index "url_crc" now contains 215141 row versions in 497 pages
DETAIL:  108343 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.06s/0.96u sec elapsed 9.13 sec.
INFO:  index "url_seed" now contains 348458 row versions in 2987 pages
DETAIL:  229515 index row versions were removed.
345 index pages have been deleted, 345 are currently reusable.
CPU 0.40s/2.38u sec elapsed 74.26 sec.
INFO:  index "url_referrer" now contains 349509 row versions in 1964
pages
DETAIL:  229515 index row versions were removed.
65 index pages have been deleted, 65 are currently reusable.
CPU 0.34s/1.53u sec elapsed 127.37 sec.
INFO:  index "url_next_index_time" now contains 349519 row versions in
3534 pages
DETAIL:  229515 index row versions were removed.
3071 index pages have been deleted, 2864 are currently reusable.
CPU 0.32s/0.67u sec elapsed 76.25 sec.
INFO:  index "url_status" now contains 349520 row versions in 3465 pages
DETAIL:  229515 index row versions were removed.
2383 index pages have been deleted, 2256 are currently reusable.
CPU 0.35s/0.85u sec elapsed 89.25 sec.
INFO:  index "url_bad_since_time" now contains 349521 row versions in
2017 pages
DETAIL:  229515 index row versions were removed.
38 index pages have been deleted, 38 are currently reusable.
CPU 0.54s/1.46u sec elapsed 83.77 sec.
INFO:  index "url_hops" now contains 349620 row versions in 3558 pages
DETAIL:  229515 index row versions were removed.
1366 index pages have been deleted, 1356 are currently reusable.
CPU 0.43s/0.91u sec elapsed 132.14 sec.
INFO:  index "url_siteid" now contains 350551 row versions in 3409 pages
DETAIL:  229515 index row versions were removed.
2310 index pages have been deleted, 2185 are currently reusable.
CPU 0.35s/1.01u sec elapsed 85.08 sec.
INFO:  index "url_serverid" now contains 350552 row versions in 3469
pages
DETAIL:  229515 index row versions were removed.
1014 index pages have been deleted, 1009 are currently reusable.
CPU 0.54s/1.01u sec elapsed 120.40 sec.
INFO:  index "url_url" now contains 346563 row versions in 6494 pages
DETAIL:  213608 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.35s/2.07u sec elapsed 285.05 sec.
INFO:  index "url_last_mod_time" now contains 346734 row versions in
1106 pages
DETAIL:  213608 index row versions were removed.
27 index pages have been deleted, 17 are currently reusable.
CPU 0.17s/0.95u sec elapsed 17.92 sec.
INFO:  "url": removed 229515 row versions in 4844 pages
DETAIL:  CPU 0.53s/1.26u sec elapsed 375.64 sec.
INFO:  "url": found 229515 removable, 310913 nonremovable row versions
in 26488 pages
DETAIL:  29063 dead row versions cannot be removed yet.
There were 3907007 unused item pointers.
192 pages are entirely empty.
CPU 7.78s/17.09u sec elapsed 3672.29 sec.
INFO:  vacuuming "pg_toast.pg_toast_127397204"
INFO:  index "pg_toast_127397204_index" now contains 0 row versions in
1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  "pg_toast_127397204": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
VACUUM

>> Is the solution to reindex data ?
>
> In 7.4 a VACUUM should be sufficient ... or at least, if it isn't
> I'd like to know why not before you destroy the evidence by reindexing.
Yes, of course.

Cordialement,
Jean-Gérard Pailloncy

[ Ah, I just got to your message with the VACUUM VERBOSE results ... ]

=?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= <pailloncy@ifrance.com> writes:
> dps=# VACUUM VERBOSE url;
> INFO:  index "url_next_index_time" now contains 349519 row versions in
> 3534 pages
> DETAIL:  229515 index row versions were removed.
> 3071 index pages have been deleted, 2864 are currently reusable.
> CPU 0.32s/0.67u sec elapsed 76.25 sec.

Hm, this is odd.  That says you've got 349519 live index entries in only
463 actively-used index pages, or an average of 754 per page, which
AFAICS could not fit in an 8K page.  Are you using a nondefault value of
BLCKSZ?  If so what?

If you *are* using default BLCKSZ then this index must be corrupt, and
what you probably need to do is REINDEX it.  But before you do that,
could you send me a copy of the index file?

            regards, tom lane

> Hm, this is odd.  That says you've got 349519 live index entries in
> only
> 463 actively-used index pages, or an average of 754 per page, which
> AFAICS could not fit in an 8K page.  Are you using a nondefault value
> of
> BLCKSZ?  If so what?
Sorry, I forgot to specify I use BLCKSZ of 32768, the same blokck's
size for newfs, the same for RAID slice's size.
I test the drive sometimes ago, and found a speed win if the slice size
the disk block size and the read block size was the same.

I do not think that a different BLCKSZ should exhibit a slowdown as the
one I found.

> If you *are* using default BLCKSZ then this index must be corrupt, and
> what you probably need to do is REINDEX it.  But before you do that,
> could you send me a copy of the index file?
Do you want the index file now, or may I try something before?

Cordialement,
Jean-Gérard Pailloncy

=?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= <pailloncy@ifrance.com> writes:
>> Are you using a nondefault value of
>> BLCKSZ?  If so what?

> Sorry, I forgot to specify I use BLCKSZ of 32768,

Okay, the numbers are sensible then.  The index density seems a bit low
(754 entries/page where the theoretical ideal would be about 1365) but
not really out-of-line.

>> could you send me a copy of the index file?

> Do you want the index file now, or may I try something before?

If you're going to reindex, please do send me a copy of the file first.

            regards, tom lane