"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes: > On Thu, Nov 08, 2007 at 04:47:09PM -0600, Pepe Barbe wrote: >> I am having an issue on PostgreSQL 8.0.12. In the past we had performance >> issues with the query planner for queries on some tables where we knew we >> had indexes and it was doing a sequential scan, and for this reason we >> issue "SET enable_seqscan = FALSE" for some queries. > This is a bad idea in general. Indeed. A less brute-force way of getting the planner to favor indexscans is to reduce random_page_cost ... have you experimented with that? Also, consider updating to 8.2.x, which has an improved cost model for indexscans and will more often make the correct choice without such shenanigans. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes: > On Thu, Nov 08, 2007 at 04:47:09PM -0600, Pepe Barbe wrote: >> I am having an issue on PostgreSQL 8.0.12. In the past we had performance >> issues with the query planner for queries on some tables where we knew we >> had indexes and it was doing a sequential scan, and for this reason we >> issue "SET enable_seqscan = FALSE" for some queries. > This is a bad idea in general. Indeed. A less brute-force way of getting the planner to favor indexscans is to reduce random_page_cost ... have you experimented with that? Also, consider updating to 8.2.x, which has an improved cost model for indexscans and will more often make the correct choice without such shenanigans. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend

"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes: > On Thu, Nov 08, 2007 at 04:47:09PM -0600, Pepe Barbe wrote: >> I am having an issue on PostgreSQL 8.0.12. In the past we had performance >> issues with the query planner for queries on some tables where we knew we >> had indexes and it was doing a sequential scan, and for this reason we >> issue "SET enable_seqscan = FALSE" for some queries. > This is a bad idea in general. Indeed. A less brute-force way of getting the planner to favor indexscans is to reduce random_page_cost ... have you experimented with that? Also, consider updating to 8.2.x, which has an improved cost model for indexscans and will more often make the correct choice without such shenanigans. regards, tom lane ---------------------------(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

"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes: > On Thu, Nov 08, 2007 at 04:47:09PM -0600, Pepe Barbe wrote: >> I am having an issue on PostgreSQL 8.0.12. In the past we had performance >> issues with the query planner for queries on some tables where we knew we >> had indexes and it was doing a sequential scan, and for this reason we >> issue "SET enable_seqscan = FALSE" for some queries. > This is a bad idea in general. Indeed. A less brute-force way of getting the planner to favor indexscans is to reduce random_page_cost ... have you experimented with that? Also, consider updating to 8.2.x, which has an improved cost model for indexscans and will more often make the correct choice without such shenanigans. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Ooops, sorry about the multiple copies there --- not sure what happened. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate