Thread: Seqscan/Indexscan still a known issue?
Hi, I find various references in the list to this issue of queries being too slow because the planner miscalculates things and decides to go for a sequenctial scan when an index is available and would lead to better performance. Is this still an issue with the latest version? I'm doing some tests right now, but I have version 7.4 (and not sure when I will be able to spend the effort to move our system to 8.2). 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. For the time being, I'm using an explicit "enable_seqscan off" in the client code, before executing the select. But I wonder: Is this still an issue, or has it been solved in the latest version? Thanks, Carlos --
Carlos Moreno skrev: > When I force it via "set enable_seqscan to off", the index scan > takes about 0.1 msec (as reported by explain analyze), whereas > > For the time being, I'm using an explicit "enable_seqscan off" > in the client code, before executing the select. But I wonder: > Is this still an issue, or has it been solved in the latest > version? For most queries it has never been an issue. Every once in a while there is a query that the planner makes a non-optimal plan for, but it's not that common. In general the optimizer has improved with every new version of pg. Almost everyone I've talked to that has upgraded has got a faster database tham before. It was like that for 7.4->8.0, for 8.0->8.1 and for 8.1->8.2. So in your case going from 7.4->8.2 is most likely going to give a speedup (especially if you have some queries that isn't just simple primary key lookups). In your case it's hard to give any advice since you didn't share the EXPLAIN ANALYZE output with us. I'm pretty sure it's possible to tune pg so it makes the right choice even for this query of yours but without the EXPLAIN ANALYZE output we would just be guessing anyway. If you want to share it then it might be helpful to show the plan both with and without seqscan enabled. How often do you run VACUUM ANALYZE; on the database? /Dennis
Carlos Moreno wrote: > > Hi, > > I find various references in the list to this issue of queries > being too slow because the planner miscalculates things and > decides to go for a sequenctial scan when an index is available > and would lead to better performance. > > Is this still an issue with the latest version? I'm doing some > tests right now, but I have version 7.4 (and not sure when I will > be able to spend the effort to move our system to 8.2). > > 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. > > For the time being, I'm using an explicit "enable_seqscan off" > in the client code, before executing the select. But I wonder: > Is this still an issue, or has it been solved in the latest > version? Please supply explain analyze for the query in both the index and sequence scan operation. We may be able to tell you why it's choosing the wrong options. Guess 1 would be that your primary key is int8, but can't be certain that is what's causing the problem. Regards Russell Smith > > Thanks, > > Carlos
On 27.01.2007, at 00:35, Russell Smith wrote: > Guess 1 would be that your primary key is int8, but can't be > certain that is what's causing the problem. Why could that be a problem? cug
> > Hi, > > I find various references in the list to this issue of queries > being too slow because the planner miscalculates things and > decides to go for a sequenctial scan when an index is available > and would lead to better performance. > > Is this still an issue with the latest version? I'm doing some > tests right now, but I have version 7.4 (and not sure when I will > be able to spend the effort to move our system to 8.2). > > 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.)? 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? 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. For example if the table has grown and was not analyzed recently, the 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. There are many such cases - the common belief that index scan is always better than the sequential scan is incorrect. But most of these cases can be identified using explain analyze output (which is missing in your post). 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. The port to 8.x might help, as some of the settings in postgresql.conf use different default values and the stats used by the planner might be 'freshier' than those in the current database. My recommendation: 1) send us the execution plan, that is use the EXPLAIN ANALYZE and send us the output 2) try to use ANALYZE on the table and run the queries again 3) review the settings in postgresql - a nice starting point is here http://www.powerpostgresql.com/PerfList (Yes, it's for Pg 8.0 but the basics are the same). Tomas
Guido Neitzer wrote: > On 27.01.2007, at 00:35, Russell Smith wrote: > >> Guess 1 would be that your primary key is int8, but can't be certain >> that is what's causing the problem. > > Why could that be a problem? Before 8.0, the planner would not choose an index scan if the types were different int8_col = const, int8_col = 4. 4 in this example is cast to int4. int8 != int4. So the planner will not choose an index scan. Regards Russell Smith > > cug > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
On Sat, 2007-01-27 at 21:44 +1100, Russell Smith wrote: > Guido Neitzer wrote: > > On 27.01.2007, at 00:35, Russell Smith wrote: > > > >> Guess 1 would be that your primary key is int8, but can't be certain > >> that is what's causing the problem. > > > > Why could that be a problem? > Before 8.0, the planner would not choose an index scan if the types were > different int8_col = const, int8_col = 4. > 4 in this example is cast to int4. int8 != int4. So the planner will > not choose an index scan. But, in 7.4 setting enable_seqscan off would not make it use that index. For the OP, the problem is likely either that the stats for the column are off, effective_cache_size is set too low, and / or random_page_cost is too high. there are other possibilities as well. FYI, I upgraded the server we use at work to scan a statistical db of our production performance, and the queries we run there, which take anywhere from a few seconds to 20-30 minutes, run much faster. About an hour after the upgrade I had a user ask what I'd done to the db to make it so much faster. The upgrade was 7.4 to 8.1 btw... still testing 8.2, and it looks very good.
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 --
Carlos Moreno <moreno_pg@mochima.com> writes:
> 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.
One reason you might consider updating is that newer versions check the
physical table size instead of unconditionally believing
pg_class.relpages/reltuples. Thus, they're much less likely to get
fooled when a table has grown substantially since it was last vacuumed
or analyzed.
regards, tom lane
Tom Lane wrote: > One reason you might consider updating is that newer versions check the > >physical table size instead of unconditionally believing >pg_class.relpages/reltuples. Thus, they're much less likely to get >fooled when a table has grown substantially since it was last vacuumed >or analyzed. > > Sounds good. Obviously, there seem to be plenty of reasons to upgrade, as pointed out in several of the previous replies; I would not rank this one as one of the top reasons to upgrade, since every time I've encountered this issue (planner selecting seq. scan when I'm convinced it should choose an index scan), I can always get away with forcing it to use an index scan, even if it feels like the wrong solution. But still, I guess what you point out comes as part of an array of improvements that will contribute to much better performance anyway! I'm sure I've said it countless times, but it feels again like the right time to say it: thank you so much for all the help and all the effort the PG team has put in making this such a great product --- improvement after improvement! Thanks, Carlos --