From: | Gregg Jaskiewicz <gryzman(at)gmail(dot)com> |
---|---|
To: | Nowak Michał <michal(dot)nowak(at)me(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query with order by and limit is very slow - wrong index used |
Date: | 2011-10-04 08:08:13 |
Message-ID: | CAJY59_jMAx9U07Xfk_iuEdKF6s6n8CR7baaWvzUYPrYTznUM3A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2011/10/4 Nowak Michał <michal(dot)nowak(at)me(dot)com>:
>
> a9-dev=> select attname, null_frac, avg_width, n_distinct, correlation from pg_stats where tablename = 'records';
> attname | null_frac | avg_width | n_distinct | correlation
> --------------------------------------+-----------+-----------+------------+-------------
> source_id | 0 | 54 | 69 | 0.303059
http://www.postgresql.org/docs/9.0/interactive/view-pg-stats.html
"Statistical correlation between physical row ordering and logical
ordering of the column values. This ranges from -1 to +1. When the
value is near -1 or +1, an index scan on the column will be estimated
to be cheaper than when it is near zero, due to reduction of random
access to the disk. (This column is null if the column data type does
not have a < operator.)"
Kind of like I and Tom said, 0.3 correlation there sounds like the cause.
Seriously, try normalisation as well, before discarding it.
--
GJ
From | Date | Subject | |
---|---|---|---|
Next Message | Nowak Michał | 2011-10-04 08:55:18 | Re: Query with order by and limit is very slow - wrong index used |
Previous Message | Gavin Flower | 2011-10-04 07:16:39 | Re: array_except -- Find elements that are not common to both arrays |