Re: [PERFORM] estimated rows vs. actual rows
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Jaime Casanova) belched out: On Sun, 13 Feb 2005 13:41:09 -0800, Josh Berkus josh@agliodbs.com wrote: Jaime, Why is this query using a seq scan rather than a index scan? Because it thinks a seq scan will be faster. I will suggest him to probe with seq scans disabled. But, IMHO, if the table has 143902 and it thinks will retrieve 2610 (almost 1.81% of the total). it won't be faster with an index? If the 2610 rows are scattered widely enough, it may be cheaper to do a seq scan. After all, with a seq scan, you read each block of the table's pages exactly once. With an index scan, you read index pages _and_ table pages, and may do and redo some of the pages. It sounds as though it's worth forcing the matter and trying it both ways and comparing them. Don't be surprised if the seq scan is in fact faster... -- select 'cbbrowne' || '@' || 'gmail.com'; http://cbbrowne.com/info/emacs.html When aiming for the common denominator, be prepared for the occasional division by zero. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] estimated rows vs. actual rows
Jaime, Why is this query using a seq scan rather than a index scan? Because it thinks a seq scan will be faster. i notice the diff between the estimated rows and actual rows (almost 2000). Yes, ANALYZE, and possibly increasing the column stats, should help that. Can this affect the query plan? i think this is a problem of statistics, am i right? if so, what can be done? Well, if the estimate was accurate, PG would be even *more* likely to use a seq scan (more rows). I think maybe you should establish whether a seq scan actually *is* faster? Perhaps do SET enable_seqscan = false and then re-run the query a few times? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] estimated rows vs. actual rows
On Sun, 13 Feb 2005 13:41:09 -0800, Josh Berkus josh@agliodbs.com wrote: Jaime, Why is this query using a seq scan rather than a index scan? Because it thinks a seq scan will be faster. I will suggest him to probe with seq scans disabled. But, IMHO, if the table has 143902 and it thinks will retrieve 2610 (almost 1.81% of the total). it won't be faster with an index? i know, i will suggest him to probe to be sure. just an opinion. regards, Jaime Casanova ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] estimated rows vs. actual rows
Jaime Casanova [EMAIL PROTECTED] writes: But, IMHO, if the table has 143902 and it thinks will retrieve 2610 (almost 1.81% of the total). it won't be faster with an index? That's almost one row in fifty. We don't know how wide the table is, but it's certainly possible that there are order-of-a-hundred rows on each page; in which case the indexscan is likely to hit every page. Twice. Not in sequence. Only if the selected rows are pretty well clustered in a small part of the table is this going to be a win over a seqscan. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] estimated rows vs. actual rows
Jaime Casanova wrote: But, IMHO, if the table has 143902 and it thinks will retrieve 2610 (almost 1.81% of the total). it won't be faster with an index? Depends on how those 2610 rows are distributed amongst the 143902. The worst case scenario is each one of them in its own page. In that case you have to read 2610 *pages*, which is probably a significant percentage of the table. You can find out this information from the pg_stats view (particularly the correlation column). Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
