Re: query plan with index having a btrim is different for strings of different length

On Dec 10, 2008, at 11:34 AM, Tom Lane wrote:

> Richard Yen <dba(at)richyen(dot)com> writes:
>> You guys are right. I tried "Miller" and gave me the same result.
>> Is
>> there any way to tune this so that for the common last names, the
>> query run time doesn't jump from <1s to >300s?
>
> If the planner's estimation is that far off then there must be
> something
> very weird about the table statistics, but you haven't given us any
> clue
> what.

Wow, thanks for helping me out here. I don't have much experience
with deconstructing queries and working with stats, so here's what I
could gather. If you need more information, please let me know.

tii=# select * from pg_stat_all_tables where relname =
'm_object_paper' or relname = 'm_assignment';
-[ RECORD 1 ]----+------------------------------
relid | 17516
schemaname | public
relname | m_assignment
seq_scan | 274
seq_tup_read | 1039457272
idx_scan | 372379230
idx_tup_fetch | 2365235708
n_tup_ins | 5641638
n_tup_upd | 520684
n_tup_del | 30339
n_tup_hot_upd | 406929
n_live_tup | 5611665
n_dead_tup | 11877
last_vacuum |
last_autovacuum | 2008-12-04 17:44:57.309717-08
last_analyze | 2008-10-20 15:09:50.943652-07
last_autoanalyze | 2008-08-15 17:16:14.588153-07
-[ RECORD 2 ]----+------------------------------
relid | 17792
schemaname | public
relname | m_object_paper
seq_scan | 83613
seq_tup_read | 184330159906
idx_scan | 685219945
idx_tup_fetch | 222892138627
n_tup_ins | 71564825
n_tup_upd | 27558792
n_tup_del | 3058
n_tup_hot_upd | 22410985
n_live_tup | 71559627
n_dead_tup | 585467
last_vacuum | 2008-10-24 14:36:45.134936-07
last_autovacuum | 2008-12-05 07:02:40.52712-08
last_analyze | 2008-11-25 14:42:04.185798-08
last_autoanalyze | 2008-08-15 17:20:28.42811-07

tii=# select * from pg_statio_all_tables where relname =
'm_object_paper' or relname = 'm_assignment';
-[ RECORD 1 ]---+---------------
relid | 17516
schemaname | public
relname | m_assignment
heap_blks_read | 22896372
heap_blks_hit | 1753777105
idx_blks_read | 7879634
idx_blks_hit | 1157729592
toast_blks_read | 0
toast_blks_hit | 0
tidx_blks_read | 0
tidx_blks_hit | 0
-[ RECORD 2 ]---+---------------
relid | 17792
schemaname | public
relname | m_object_paper
heap_blks_read | 2604944369
heap_blks_hit | 116307527781
idx_blks_read | 133534908
idx_blks_hit | 3601637440
toast_blks_read | 0
toast_blks_hit | 0
tidx_blks_read | 0
tidx_blks_hit | 0

Also, yes, we've kicked around the idea of doing an index on the
concatenation of the first and last names--that would definitely be
more unique, and I think we're actually going to move to that. Just
thought I'd dig deeper here to learn more.

Thanks!
--Richard