Re: Seqscan/Indexscan still a known issue? - Mailing list pgsql-performance

Tomas Vondra wrote:

>>
>>When I force it via  "set enable_seqscan to off", the index scan
>>takes about 0.1 msec  (as reported by explain analyze), whereas
>>with the default, it chooses a seq. scan, for a total execution
>>time around 10 msec!!  (yes: 100 times slower!).  The table has
>>20 thousand records, and the WHERE part of the query uses one
>>field that is part of the primary key  (as in, the primary key
>>is the combination of field1,field2, and the query involves a
>>where field1=1 and some_other_field=2).  I don't think I'm doing
>>something "wrong", and I find no reason not to expect the query
>>planner to choose an index scan.
>>
>
>1) I'm missing a very important part - information about the settings
>   in postgresql.conf, especially effective cache size, random page
>   cost, etc. What hw are you using (RAM size, disk speed etc.)?
>

show all;  responds with  (I'm leaving only the ones I think could be
the relevant ones):

 client_encoding                | SQL_ASCII
 commit_delay                   | 0
 commit_siblings                | 5
 cpu_index_tuple_cost           | 0.001
 cpu_operator_cost              | 0.0025
 cpu_tuple_cost                 | 0.01
 deadlock_timeout               | 1000
 effective_cache_size           | 1000
 enable_hashagg                 | on
 enable_hashjoin                | on
 enable_indexscan               | on
 enable_mergejoin               | on
 enable_nestloop                | on
 enable_seqscan                 | on
 enable_sort                    | on
 enable_tidscan                 | on
 from_collapse_limit            | 8
 fsync                          | on
 geqo                           | on
 geqo_effort                    | 1
 geqo_generations               | 0
 geqo_pool_size                 | 0
 geqo_selection_bias            | 2
 geqo_threshold                 | 11
 join_collapse_limit            | 8
 max_connections                | 100
 max_expr_depth                 | 10000
 max_files_per_process          | 1000
 max_fsm_pages                  | 20000
 max_fsm_relations              | 1000
 max_locks_per_transaction      | 64
 pre_auth_delay                 | 0
 random_page_cost               | 4
 regex_flavor                   | advanced
 server_encoding                | SQL_ASCII
 server_version                 | 7.4.5
 shared_buffers                 | 62000
 sort_mem                       | 1024
 statement_timeout              | 0
 vacuum_mem                     | 8192
 virtual_host                   | unset
 wal_buffers                    | 8
 wal_debug                      | 0
 wal_sync_method                | fdatasync


Any obvious red flag on these?

The HW/SW is:  Fedora Core 2 running on a P4 3GHz HT, with 1GB of
RAM and 120GB SATA drive.

>
>2) Another thing I'm missing is enough information about the table
>   and the query itself. What is the execution plan used? Was the table
>   modified / vacuumed / analyzed recently?
>

I vacuum analyze the entire DB daily, via a cron entry (at 4AM).

But I think the problem is that this particular table had not been
vacuum analyzed after having inserted the 20000 records  (the
query planner was giving me seq. scan when the table had about
a dozen records --- and seq. scan was, indeed, 10 times faster;
as a test, to make sure that the query planner would do the right
thing when the amount of records was high, I inserted 20000
records, and tried again --- now the seq. scan was 100 times
slower, but it was still chosen  (at that point was that I did a
search through the archives and then posted the question).

But now, after reading the replies, I did a vacuum analyze for
this table, and now the query planner is choosing the Index
scan.

>Without these information it's completely possible the postgresql is
>using invalid values and thus generating suboptimal execution plan.
>There are many cases when the sequential scan is better (faster, does
>less I/O etc.) than the index scan.
>

But as the tests yesterday revealed, this was not the case
(explain analyze was reporting execution times showing index
scan 100 times faster!)

>For example if the table has grown and was not analyzed recently
>

Ok, now I'm quite sure that this is, indeed, the case  (as
you can see from my description above)

>postgresql may still believe it's small and thus uses the sequential
>scan. Or maybe the effective case size is set improperly (too low in
>this case) thus the postgresql thinks just a small fraction of data is
>cached, which means a lot of scattered reads in case of the index -
>that's slower than sequential reads.
>

But these values are all defaults  (I think I played with the shared
buffers size, following some guidelines I read in the PostgreSQL
documentation), which is why I felt that I was not doing something
"wrong" which would be at fault for making the query planner do
the wrong thing  (well, nothing wrong in the query and the table
definition --- there was indeed something wrong on my side).

>There are many such cases - the common belief that index scan is always
>better than the sequential scan is incorrect.
>

No, you can rest assured that this was not the case with me!  I
do understand that basic notion that, for example, the bubble
sort is faster than most NlogN sort algorithms if you have an
array of 3 or 4 elements  (or depending on the nature of the
data, if they are always close to sorted, etc.)

One thing is that, the cases where seq. scan are faster tend
to be when there aren't many records, and therefore, the
execution times are low anyway --- this seems like an argument
in favor of being biased in favor of index scans;  but yes, I
guess if one has to do several thousands queries like those,
then the fact that the query takes 0.5 ms instead of 1 ms
does count.

>But most of these cases
>can be identified using explain analyze output (which is missing in your
>post).
>
I can't reproduce now teh seq. scan, and given that I seem to
have found the reason for the unexpected result, there probably
is no point in showing you something that exhibits no problem
to be debugged.

>
>The data supplied by you are not a 'proof' the index scan is better than
>sequential scan in this case, as the data might be cached due to
>previous queries.
>

No, that was not the case  (another notion that is quite clear
in my mind :-)).  I repeated ll queries no less than 10 times ---
then alternating with enable_seqscan on and off, etc.).  The times
I supplied seemed to be the "asymptotic" values, once things had
been cached as much as they would.

>
>The port to 8.x might help
>
Given this, and some of the other replies, I guess I'll move
this way up in my list of top-priority things to do.

>
>2) try to use ANALYZE on the table and run the queries again
>

Bingo!  :-)

>
>3) review the settings in postgresql - a nice starting point is here
>
>   http://www.powerpostgresql.com/PerfList
>

I'll definitely take a look at this, so that I can figure thigns
out better if something similar arises in the future.

Thanks,

Carlos
--