Re: ERROR: invalid restriction selectivity: 224359728.000000

> On Nov 16, 2007 11:59 AM, <xeb(at)mail(dot)ru> wrote:
> > Hello!
> > Process postmaster completly eat my proccessor for a long time and i see
> > that message in logs.
> > Does anybody know what does the subj means and why it occures ?
>
> You're giving us WAY too little information to troubleshoot this problem.
>
> What message in the logs? What does top / vmstat / ps / iostat really
> show? What kind of query is running? Have you got query logging
> turned on for long running queries? Is this actually affecting the
> performance of your machine? Is the process chewing through all your
> memory? If you know what query is causing this, what does explain
> analyze of the query look like?
>
> The more info you can provide about the issue, the more we can help.
> But first we have to be sure there's a problem.
>
> If you're asking postgresql to sort 1billion rows it is going to eat
> up a lot of CPU and there's little you can do about it.

Tasks: 110 total, 21 running, 88 sleeping, 0 stopped, 1 zombie
Cpu(s): 91.0%us, 0.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 9.0%si, 0.0%st
Mem: 516176k total, 506716k used, 9460k free, 3912k buffers
Swap: 987956k total, 52656k used, 935300k free, 86928k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3327 postgres 16 0 36856 11m 10m R 14.4 2.3 2:51.27 postmaster
3439 postgres 16 0 36856 11m 10m R 14.4 2.3 0:25.17 postmaster
3300 postgres 16 0 36856 11m 10m R 13.8 2.4 3:29.03 postmaster
3383 postgres 16 0 36884 11m 10m R 13.8 2.3 0:43.03 postmaster
3364 postgres 16 0 36856 11m 10m R 10.8 2.3 1:01.03 postmaster
3288 postgres 16 0 36856 12m 11m R 10.2 2.5 4:27.12 postmaster
3338 postgres 16 0 36856 11m 10m R 9.0 2.3 1:51.57 postmaster
3360 postgres 16 0 36860 11m 10m R 8.4 2.3 1:12.65 postmaster
3278 postgres 16 0 36856 13m 11m R 5.4 2.6 5:46.71 postmaster
1 root 15 0 2960 976 936 S 0.0 0.2 0:01.14 init

# cat /proc/vmstat
nr_anon_pages 98383
nr_mapped 7015
nr_file_pages 24724
nr_slab_reclaimable 1018
nr_slab_unreclaimable 2128
nr_page_table_pages 878
nr_dirty 3
nr_writeback 0
nr_unstable 0
nr_bounce 0
nr_vmscan_write 16557
pgpgin 5992958
pgpgout 3674444
pswpin 4297
pswpout 13350
pgalloc_dma 203495
pgalloc_normal 11341678
pgalloc_high 0
pgfree 11548090
pgactivate 886342
pgdeactivate 883820
pgfault 37704524
pgmajfault 4025
pgrefill_dma 335184
pgrefill_normal 10848190
pgrefill_high 0
pgsteal_dma 47787
pgsteal_normal 1912761
pgsteal_high 0
pgscan_kswapd_dma 44246
pgscan_kswapd_normal 1627840
pgscan_kswapd_high 0
pgscan_direct_dma 12545
pgscan_direct_normal 312576
pgscan_direct_high 0
pginodesteal 1292
slabs_scanned 2673920
kswapd_steal 1643732
kswapd_inodesteal 14384
pageoutrun 31142
allocstall 4905
pgrotated 13776

error message in log:
ERROR: invalid restriction selectivity: 0.000049
COMMAND: SELECT u.username, "ОС", u.user_id, u.user_level,u.user_posts,
u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim,
u.user_yim, u.user_regdate, u.user_msnm, u.user_jabber, u.user_viewemail,
u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar,
u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_ou_sig,
u.user_ou_avatar, u.user_show_jabber_status, p.*, pt.post_text,
pt.post_subject, pt.bbcode_uid
FROM posts p, users u, posts_text pt
WHERE p.topic_id = 1721

AND pt.post_id = p.post_id
AND u.user_id = p.poster_id
ORDER BY p.post_time ASC
LIMIT 15 OFFSET 0;

After postgres restart:
phpbb=# explain SELECT u.username, "OS", u.user_id, u.user_level,u.user_posts,
u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim,
u.user_yim, u.user_regdate, u.user_msnm, u.user_jabber, u.user_viewemail,
u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar,
u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_ou_sig,
u.user_ou_avatar, u.user_show_jabber_status, p.*, pt.post_text,
pt.post_subject, pt.bbcode_uid
FROM posts p, users u, posts_text pt
WHERE p.topic_id = 1721
AND pt.post_id = p.post_id
AND u.user_id = p.poster_id
ORDER BY p.post_time ASC
LIMIT 15 OFFSET 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Limit (cost=264.27..264.30 rows=15 width=522)
-> Sort (cost=264.27..264.31 rows=16 width=522)
Sort Key: p.post_time
-> Nested Loop (cost=0.00..263.95 rows=16 width=522)
-> Nested Loop (cost=0.00..175.46 rows=16 width=371)
-> Index Scan using topic_id_posts_index on posts p
(cost=0.00..50.95 rows=16 width=56)
Index Cond: (topic_id = 1721)
-> Index Scan using posts_text_pkey on posts_text pt
(cost=0.00..7.77 rows=1 width=319)
Index Cond: (pt.post_id = p.post_id)
-> Index Scan using users_pkey on users u (cost=0.00..5.52
rows=1 width=151)
Index Cond: (u.user_id = p.poster_id)
(11 rows)