Re: [PERFORM] Seqscan/Indexscan still a known issue?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 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. 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Seqscan/Indexscan still a known issue?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Seqscan/Indexscan still a known issue?
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Seqscan/Indexscan still a known issue?
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 | 1 max_files_per_process | 1000 max_fsm_pages | 2 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 2 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 2 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
Re: [PERFORM] Seqscan/Indexscan still a known issue?
Carlos Moreno [EMAIL PROTECTED] writes: But I think the problem is that this particular table had not been vacuum analyzed after having inserted the 2 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 2 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Seqscan/Indexscan still a known issue?
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 -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] 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 -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Seqscan/Indexscan still a known issue?
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Seqscan/Indexscan still a known issue?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
