From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org>, "Greg Stark" <gsstark(at)mit(dot)edu> |
Subject: | Re: Bad query optimisation |
Date: | 2002-11-30 22:57:05 |
Message-ID: | 0c1301c298c3$cdb21540$6500a8c0@internal |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> This is weird, it seems like min and max aren't being optimised
symmetrically.
> It seems like both of these should result in similar plans and run equally
> fast. Instead the first is actually really slow and the second is
perfectly
> quick.
Without knowing anything about your data, if Postgres knows (from its stats
tables) that 90% of the values in your column are above 'K0C1N2' then it
will of course do a seq scan for the second query.
If that is incorrect, then have your gone 'ANALYZE postalcodes' recently?
Cheers,
Chris
> foo=# explain select max(postalcode) from postalcodes where postalcode <
'K0C1N2';
>
> Aggregate (cost=123.59..123.59 rows=1 width=10)
> -> Index Scan using postalcodes_pkey on postalcodes (cost=0.00..120.50
rows=1234 width=10)
>
>
> foo=# explain select min(postalcode) from postalcodes where postalcode >
'K0C1N2';
>
> Aggregate (cost=10373.45..10373.45 rows=1 width=10)
> -> Seq Scan on postalcodes (cost=0.00..9697.11 rows=270535 width=10)
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2002-11-30 23:23:56 | Re: Bad query optimisation |
Previous Message | Greg Stark | 2002-11-30 22:47:54 | Bad query optimisation |