Re: [PERFORM] Subquery WHERE IN or WHERE EXISTS faster?
Hi, Yes that looks strange. But it is not possible that I have processors in users_processors which do not appear in processors, because users_processors contains foreign keys to processors. If I remove the LIMIT 10 OFFSET 1 the line Sort (cost= rows=11.. disappears and the query return 13 correct processors from processors. Then, I have tested different values for OFFSET. If I set Offset to 2 and LIMIT=10 the line is: Sort (cost=113.73..113.75 rows=8 width=5) (actual time=0.322..0.330 rows=12 loops=1) If I set Offset to 3 and LIMIT=10 it is Sort (cost=113.73..113.75 rows=8 width=5) (actual time=0.321..0.328 rows=13 loops=1) It looks like if this row is something like min(max_rows=13, LIMIT+OFFSET). But I do not completely understand the Syntax... ;-) Kind regards Ulrich Gregory Stark wrote: Ulrich [EMAIL PROTECTED] writes: EXPLAIN ANALYZE SELECT speed FROM processors WHERE id IN (SELECT processorid FROM users_processors WHERE userid=4040) ORDER BY speed ASC LIMIT 10 OFFSET 1; Limit (cost=113.73..113.75 rows=7 width=5) (actual time=0.335..0.340 rows=10 loops=1) - Sort (cost=113.73..113.75 rows=8 width=5) (actual time=0.332..0.333 rows=11 loops=1) ^^ Sort Key: processors.speed Sort Method: quicksort Memory: 17kB - Nested Loop (cost=47.22..113.61 rows=8 width=5) (actual time=0.171..0.271 rows=13 loops=1) - HashAggregate (cost=47.22..47.30 rows=8 width=4) (actual time=0.148..0.154 rows=13 loops=1) - Bitmap Heap Scan on users_processors (cost=4.36..47.19 rows=12 width=4) (actual time=0.074..0.117 rows=13 loops=1) ^^ Index Cond: (userid = 4040) - Index Scan using processors_pkey on processors (cost=0.00..8.28 rows=1 width=9) (actual time=0.006..0.007 rows=1 loops=13) Index Cond: (processors.id = users_processors.processorid) It looks to me like you have some processors which appear in users_processors but not in processors. I don't know your data model but that sounds like broken referential integrity to me. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Subquery WHERE IN or WHERE EXISTS faster?
Ulrich [EMAIL PROTECTED] writes: Hi, Yes that looks strange. But it is not possible that I have processors in users_processors which do not appear in processors, because users_processors contains foreign keys to processors. If I remove the LIMIT 10 OFFSET 1 the line Sort (cost= rows=11.. disappears and the query return 13 correct processors from processors. Oh, er, my bad. That makes perfect sense. The actual numbers can be affected by what records are actually requested. The LIMIT prevents the records beyond 11 from ever being requested even though they exist. While the bitmap heap scan has to fetch all the records even though they don't all get used, the nested loop only fetches the records as requested. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] A guide/tutorial to performance monitoring and tuning
On Fri, 27 Jun 2008, Nikhil G. Daddikar wrote: I have been searching on the net on how to tune and monitor performance of my postgresql server but not met with success. A lot of information is vague and most often then not the answer is it depends. That's because it does depend. I collect up the best of resources out there and keep track of them at http://wiki.postgresql.org/wiki/Performance_Optimization so if you didn't find that yet there's probably some good ones you missed. Right now I'm working with a few other people to put together a more straightforward single intro guide that should address some of the vagueness you point out here, but that's still a few weeks away from being ready. Monitoring performance isn't really covered in any of this though. Right now the best simple solution out there is probably Nagios with the PostgreSQL plug-in. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] sequence scan problem
John Beaver wrote: I'm having a strange problem with a query. The query is fairly simple, with a few constants and two joins. All relevant columns should be indexed, and I'm pretty sure there aren't any type conversion issues. But the query plan includes a fairly heavy seq scan. The only possible complication is that the tables involved are fairly large - hundreds of millions of rows each. Can anyone explain this? There should only ever be a maximum of about 50 rows returned when the query is executed. You didn't say when you last vacuumed? If there should only be 50 rows returned then the estimates from the planner are way out. If that doesn't help, we'll need version info, and (if you can afford the time) an explain analyze Cheers, Jeremy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] sequence scan problem
Oh, and the version is 8.3.3. Jeremy Harris wrote: John Beaver wrote: I'm having a strange problem with a query. The query is fairly simple, with a few constants and two joins. All relevant columns should be indexed, and I'm pretty sure there aren't any type conversion issues. But the query plan includes a fairly heavy seq scan. The only possible complication is that the tables involved are fairly large - hundreds of millions of rows each. Can anyone explain this? There should only ever be a maximum of about 50 rows returned when the query is executed. You didn't say when you last vacuumed? If there should only be 50 rows returned then the estimates from the planner are way out. If that doesn't help, we'll need version info, and (if you can afford the time) an explain analyze Cheers, Jeremy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] sequence scan problem
John Beaver [EMAIL PROTECTED] writes: Can anyone explain this? There should only ever be a maximum of about 50 rows returned when the query is executed. Is the estimate that 197899 rows of gene_prediction_view have go_term_ref = 2 about right? If not, then we need to talk about fixing your statistics. If it is in the right ballpark then I do not see *any* plan for this query that runs in small time. The only way to avoid a seqscan on functional_linkage_scores would be to do 198K^2 index probes into it, one for each combination of matching fs1 and fs2 rows; I can guarantee you that that's not a win. The fact that the planner is estimating 352770803726 result rows compared to your estimate of 50 offers some hope that it's a stats problem, but ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Out of memory for Select query.
All, While running a Select query we get the below error: ERROR: out of memory DETAIL: Failed on request of size 192. Postgres Conf details: shared_buffers = 256000 work_mem =15 max_stack_depth = 16384 max_fsm_pages = 40 version: 8.1.3 We are using 8gb of Primary memory for the server which is used as a dedicated database machine. The data log shows the below message after getting the Out of memory error. Also attached the explain for the query. Can someone let us know , if have some worng parameter setup or any solution to the problem? Regards, Nimesh. TopMemoryContext: 57344 total in 6 blocks; 9504 free (12 chunks); 47840 used TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks); 6328 used Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used MessageContext: 1040384 total in 7 blocks; 263096 free (4 chunks); 777288 used JoinRelHashTable: 8192 total in 1 blocks; 3888 free (0 chunks); 4304 used smgr relation table: 8192 total in 1 blocks; 1840 free (0 chunks); 6352 used Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used PortalHeapMemory: 1024 total in 1 blocks; 856 free (0 chunks); 168 used ExecutorState: 122880 total in 4 blocks; 51840 free (6 chunks); 71040 used HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used HashBatchContext: 2089044 total in 8 blocks; 573232 free (12 chunks); 1515812 used HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used HashBatchContext: 2080768 total in 7 blocks; 749448 free (11 chunks); 1331320 used HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used HashBatchContext: 245760 total in 4 blocks; 109112 free (4 chunks); 136648 used HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used HashBatchContext: 1032192 total in 6 blocks; 504104 free (8 chunks); 528088 used HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used HashBatchContext: 1032192 total in 6 blocks; 474456 free (8 chunks); 557736 used HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used HashBatchContext: 2080768 total in 7 blocks; 783856 free (11 chunks); 1296912 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used . . . AggContext: 941613056 total in 129 blocks; 13984 free (154 chunks); 941599072 used TupleHashTable: 113303576 total in 24 blocks; 1347032 free (74 chunks); 111956544 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used CacheMemoryContext: 516096 total in 6 blocks; 12080 free (0 chunks); 504016 used rg_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used rg_idx: 1024 total in 1 blocks; 256 free (0 chunks); 768 used rg_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used rc_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_c_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_c_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_ch_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_ch_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_ch_cd: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_cm_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_c_m_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_s_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_p_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_p_cd_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_a_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_a_v_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_d_sqldt_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_da_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_nw_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_n_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used r_m_network_date_idx: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328
Re: [PERFORM] Subquery WHERE IN or WHERE EXISTS faster?
Jaime Casanova [EMAIL PROTECTED] writes: i've made some queries run faster using EXISTS instead of large IN clauses... actually, it was NOT EXISTS replacing a NOT IN That's just about entirely unrelated ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
