Re: Index Backward Scan fast / Index Scan slow !

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