Re: Request for help with slow query

Woolcock, Sean wrote:

> A description of what you are trying to achieve and what results
> you expect:
>  I have a large (3 million row) table called "tape" that represents
>  files, which I join to a small (100 row) table called "filesystem"
>  that represents filesystems. I have a web interface that allows
>  you to sort by a number of fields in the tape table and view the
>  results 100 at a time (using LIMIT and OFFSET).

Higher OFFSET settings may be slow because it has to read through
OFFSET result rows before returning anything. There are other ways
this problem can be solved, like saving key values at both ends of
the displayed range.

>  On Postgres 8.1.17 this takes about 60 seconds. I would like it to
>  be faster.

There was a major overall speed improvement in 8.2. And another in
8.3. Etc. 8.1 has been out of support for about two years now.

http://www.postgresql.org/support/versioning/

>  1. I added an index on last_write_date with:
>
>  create index tape_last_write_date_idx on tape(last_write_date);
>
>  and there was no improvement in query time.

I was going to ask whether you tried an index on tape
(last_write_date DESC) -- but that feature was added in 8.3.  Never
mind.

>  2. I bumped:
>  effective_cache_size to 1/2 system RAM (1GB)
>  shared_buffers to 1/4 system RAM (512MB)
>  work_mem to 10MB
>  and there was no improvement in query time.

Not bad adjustments probably, anyway.

>  3. I ran the query against the same data in Postgres 9.1.6 rather
>  than 8.1.17 using the same hardware and it was about 5 times
>  faster (nice work, whoever did that!). Unfortunately upgrading is
>  not an option,

That is unfortunate.

> CPU manufacturer and model:
>  Intel Celeron CPU 440 @ 2.00GHz
>
> Amount and size of RAM installed:
>  2GB RAM
>
> Storage details (important for performance and corruption
> questions):
>
>  Do you use a RAID controller?
>  No.
>  How many hard disks are connected to the system and what types are
>  they?
>  We use a single Hitachi HDT72102 SATA drive (250GB) 7200 RPM.
>  How are your disks arranged for storage?
>  Postgres lives on the same 100GB ext3 partition as the OS.

That's not exactly blazingly fast hardware. If you value that data at
all, I hope you have paid a lot of attention to backups, because that
sounds like a machine likely to have a drive over 5 years old, which
makes it highly likely to fail hard without a lot of advance warning.

You seem to be heavily cached. Have you tried these settings?:

seq_page_cost = 0.1
random_page_cost = 0.1
cpu_tuple_cost = 0.03

That might encourage it to use that index you added. Well, if a
version of PostgreSQL that old did reverse index scans. If not you
might be able to add a functional index and coax it into use.

-Kevin