Re: Interesting new bug?

Tim Perdue <tim@sourceforge.net> writes:
> I'm attempting to select out of a large table (10GB) with about 4
> million rows, and it winds up just sitting and doing "nothing" forever.

> db_geocrawler=# explain SELECT * FROM tbl_mail_archive WHERE
> fld_mail_list=0 ORDER BY fld_mailid ASC LIMIT 10 OFFSET 0;
> NOTICE:  QUERY PLAN:

> Index Scan using tbl_mail_archive_pkey on tbl_mail_archive 
> (cost=0.00..6402391.68 rows=19357 width=80)

Interesting.  Since there's no explicit sort in the plan, I infer that
index tbl_mail_archive_pkey is on fld_mailid, meaning that the indexscan
yields data already sorted by fld_mailid --- otherwise a sort step would
be needed.  Evidently the optimizer is guessing that "scan in fld_mailid
order until you have 10 rows where fld_mail_list=0" is faster than
"find all rows with fld_mail_list=0 and then sort by fld_mailid".

Since you're complaining, I guess that this is not so :-( ... but I'm
not sure how the optimizer might be taught to guess that.  What exactly
are the indexes *on* here; how many rows are in the table; and how many
rows satisfy fld_mail_list=0?
        regards, tom lane