From: | Tim Perdue <tim(at)sourceforge(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)hub(dot)org |
Subject: | Re: Interesting new bug? |
Date: | 2000-08-23 04:56:18 |
Message-ID: | 39A35972.93B8247E@sourceforge.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
>
> Tim Perdue <tim(at)sourceforge(dot)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.
Tim
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Johannsen | 2000-08-23 05:06:26 | Re: [Solved] SQL Server to PostgreSQL |
Previous Message | Tom Lane | 2000-08-23 04:46:13 | Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan |