Re: Performance Issues

A long time ago, in a galaxy far, far away, ravi(dot)ramachandra(at)wipro(dot)com ("Ravi T Ramachandra") wrote:
> I recently setup postgres on a Linux box with 4GB Ram and 2.5 GHz
> processor.   We have created a database with 1.5 million rows in a
> table.  When we try to select rows from the table, it is taking
> enormous time with the default configuration.   It takes 2 to 3
> seconds to select 1 row that has been selected with indexed columns.
>
> SELECT * FROM TABLE A WHERE COL1 = 1 AND COL2 = 'ABC'.  
>
> We have created index definition as follows
>
> CREATE INDEX IDX ON A(COL1, COL2);
>
> Explain on the above statement shows it is sequential scan.   The
> process size for the postmaster shows as 4MB (is this normal ?)

The size seems normal for a database with default parameters. You
might want to do some tuning of parameters in postgresql.conf to
indicate the realistic size of your hardware, instead of its *very*
conservative assumptions.

And as for the SEQ SCAN, there are two most likely reasons:

1. If the query planner thinks that "most" of the rows will be
returned by the query, then it would indeed be preferable to do a seq
scan.

Somehow, I doubt that's the case here, but this sort of thing *does*
happen, and surprises people...

2. Did you ever run ANALYZE on the table to give the query planner
some statistics on what actually is in the table?

If there are no useful stats (in pg_statistic), then the query planner
will do a seq scan because it has no reason to prefer anything else.

Run VACUUM ANALYZE VERBOSE; on the database, and see if that changes
things. I would surely expect it to...
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://cbbrowne.com/info/postgresql.html
"But life wasn't yes-no, on-off. Life was shades of gray, and
rainbows not in the order of the spectrum."
-- L. E. Modesitt, Jr., _Adiamante_