Re: ERROR: invalid restriction selectivity: 224359728.000000

> On Nov 16, 2007 11:59 AM,  <xeb@mail.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)