What did you think of this? I fixed my problem by changing my query -
but I shouldn't have had to. This looks like a weakness in your
optimizer, having to first sort on criteria that you don't care about.
Tim
Tim Perdue wrote:
>
> Tom Lane wrote:
> >
> > 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?
>
> There is an index on fld_mail_list and there were 1093 rows that matched
> out of about 4.1 million.
>
> I wonder if this is the same problem we had before where I need to order
> by fld_mail_list, fld_mailid instead of just on fld_mailid. If so, you
> need to get that fixed in the optimizer.
>
> db_geocrawler=# explain
> db_geocrawler-# SELECT * FROM tbl_mail_archive WHERE
> db_geocrawler-# fld_mail_list=0 ORDER BY fld_mail_list ASC,fld_mailid
> ASC LIMIT 10 OFFSET 0;
> NOTICE: QUERY PLAN:
>
> Sort (cost=78282.54..78282.54 rows=19357 width=80)
> -> Index Scan using idx_archive_list on tbl_mail_archive
> (cost=0.00..76904.24 rows=19357 width=80)
>
> EXPLAIN
>
> Notice how it is now using the right index, because I am doing a sort on
> fld_mail_list first.
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723