[PERFORM] Sequencial scan in a JOIN
Hi everyone, I am trying to run the following query: SELECT count(1) --DISTINCT l_userqueue.queueid FROM e_usersessions JOIN l_userqueue ON l_userqueue.userid = e_usersessions.entityid JOIN a_activity ON a_activity.activequeueid = l_userqueue.queueid AND a_activity.vstatus= 1 AND a_activity.ventrydate 0 AND a_activity.sbuid = e_usersessions.sbuid AND a_activity.assignedtoid = 0 AND a_activity.status '0' WHERE e_usersessions.sessionkeepalivedatetime 20120605082131943 Explain analyze: 'Aggregate (cost=100402.10..100402.11 rows=1 width=0) (actual time=2249.051..2249.051 rows=1 loops=1)' ' - Hash Join (cost=10.93..99795.09 rows=242803 width=0) (actual time=0.541..2249.027 rows=33 loops=1)' 'Hash Cond: ((a_activity.activequeueid = l_userqueue.queueid) AND (a_activity.sbuid = e_usersessions.sbuid))' '- Seq Scan on a_activity (cost=0.00..88462.52 rows=1208167 width=22) (actual time=0.010..1662.142 rows=1207855 loops=1)' ' Filter: ((ventrydate 0) AND ((status)::text '0'::text) AND (vstatus = 1) AND (assignedtoid = 0::numeric))' '- Hash (cost=10.86..10.86 rows=5 width=22) (actual time=0.053..0.053 rows=4 loops=1)' ' - Hash Join (cost=9.38..10.86 rows=5 width=22) (actual time=0.033..0.048 rows=4 loops=1)' 'Hash Cond: (l_userqueue.userid = e_usersessions.entityid)' '- Seq Scan on l_userqueue (cost=0.00..1.23 rows=23 width=27) (actual time=0.003..0.009 rows=23 loops=1)' '- Hash (cost=9.31..9.31 rows=5 width=21) (actual time=0.018..0.018 rows=2 loops=1)' ' - Index Scan using i06_e_usersessions on e_usersessions (cost=0.00..9.31 rows=5 width=21) (actual time=0.009..0.012 rows=2 loops=1)' 'Index Cond: (sessionkeepalivedatetime 20120605082131943::bigint)' 'Total runtime: 2249.146 ms' I am trying to understand the reason why the a sequencial scan is used on a_activity instead of using the index by activequeueid (i08_a_activity). If I run the this other query, I get a complete different results: SELECT * FROM a_activity WHERE a_activity.activequeueid = 123456 AND a_activity.vstatus= 1 AND a_activity.ventrydate 0 Explain analyze: 'Index Scan using i08_a_activity on a_activity (cost=0.00..303.57 rows=162 width=7287) (actual time=0.019..0.019 rows=0 loops=1)' ' Index Cond: ((activequeueid = 123456::numeric) AND (vstatus = 1) AND (ventrydate 0))' 'Total runtime: 0.076 ms' This is the definition of the index : CREATE INDEX i08_a_activity ON a_activity USING btree (activequeueid , vstatus , ventrydate ); a_activity table has 1,216,134 rows Thanks in advance, Andrew
Re: [PERFORM] Sequencial scan in a JOIN
On 06/05/2012 07:48 AM, Andrew Jaimes wrote: ' - Hash Join (cost=10.93..99795.09 rows=242803 width=0) (actual time=0.541..2249.027 rows=33 loops=1)' 'Hash Cond: ((a_activity.activequeueid = l_userqueue.queueid) AND (a_activity.sbuid = e_usersessions.sbuid))' '- Seq Scan on a_activity (cost=0.00..88462.52 rows=1208167 width=22) (actual time=0.010..1662.142 I'd be willing to bet your stats are way, way off. It expected 242,803 rows in the hash, but only got 33. In that kind of scenario, I could easily see the planner choosing a sequence scan over an index scan, as doing that many index seeks would be much more expensive than scanning the table. What's your default_statistics_target, and when is the last time you analyzed the tables in this query? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Sequencial scan in a JOIN
the default_statistics_target is set to 200, and I have run the analyze and reindex on these tables before writing the email. Andrew Date: Tue, 5 Jun 2012 08:15:45 -0500 From: stho...@optionshouse.com To: andrewjai...@hotmail.com CC: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Sequencial scan in a JOIN On 06/05/2012 07:48 AM, Andrew Jaimes wrote: ' - Hash Join (cost=10.93..99795.09 rows=242803 width=0) (actual time=0.541..2249.027 rows=33 loops=1)' 'Hash Cond: ((a_activity.activequeueid = l_userqueue.queueid) AND (a_activity.sbuid = e_usersessions.sbuid))' '- Seq Scan on a_activity (cost=0.00..88462.52 rows=1208167 width=22) (actual time=0.010..1662.142 I'd be willing to bet your stats are way, way off. It expected 242,803 rows in the hash, but only got 33. In that kind of scenario, I could easily see the planner choosing a sequence scan over an index scan, as doing that many index seeks would be much more expensive than scanning the table. What's your default_statistics_target, and when is the last time you analyzed the tables in this query? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Sequencial scan in a JOIN
On 06/05/2012 08:31 AM, Andrew Jaimes wrote: the default_statistics_target is set to 200, and I have run the analyze and reindex on these tables before writing the email. Out of idle curiosity, how do these two variants treat you? SELECT count(1) FROM e_usersessions s JOIN l_userqueue q ON (q.userid = s.entityid) JOIN a_activity a ON (a.activequeueid = q.queueid) WHERE s.sessionkeepalivedatetime 20120605082131943 AND a.vstatus= 1 AND a.ventrydate 0 AND a.sbuid = s.sbuid AND a.assignedtoid = 0 AND a.status '0' SELECT count(1) FROM e_usersessions s JOIN l_userqueue q ON (q.userid = s.entityid) WHERE s.sessionkeepalivedatetime 20120605082131943 AND EXISTS ( SELECT 1 FROM a_activity a WHERE a.activequeueid = q.queueid AND a.sbuid = s.sbuid AND a.vstatus = 1 AND a.ventrydate 0 AND a.assignedtoid = 0 AND a.status'0' ) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Sequencial scan in a JOIN
On 06/05/2012 09:41 AM, Andrew Jaimes wrote: The second query ran better than the first one: That's what I figured. Ok, so looking back to your original message again: CREATE INDEX i08_a_activity ON a_activity USING btree (activequeueid , vstatus , ventrydate ); Based on the query here, it doesn't appear that vstatus or ventrydate are doing you any good in that index. Nor would your query even really make use of them anyway, considering their catch-all equalities. If you can make a clone of a_activity, could you try this index instead with your original query: CREATE INDEX idx_a_activity_queue ON a_activity_clone (activequeueid); Then compare to this: CREATE INDEX idx_a_activity_queue_sbuid ON a_activity_clone (activequeueid, sbuid); And the results of this query would also be handy: SELECT attname, n_distinct FROM pg_stats WHERE tablename='a_activity'; Generally you want to order your composite indexes in order of uniqueness, if you even want to make a composite index in the first place. I noticed in both cases, it's basically ignoring sbuid aside from the implied hash to exclude non-matches. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Missing block Magic
Hi,
I want to make a function in C for postgresql, this is the code:
#define _USE_32BIT_TIME_T
#define BUILDING_DLL 1
#include postgres.h
#include fmgr.h
#include executor\spi.h /* SPI - Server Programming Interface */
#if defined(_MSC_VER) || defined(__MINGW32__)
#define PG_GETINF_EXPORT __declspec (dllexport)
#else
#define PG_GETINF_EXPORT
#endif
PG_MODULE_MAGIC;
PG_GETINF_EXPORT PG_FUNCTION_INFO_V1(suma);
Datum suma(PG_FUNCTION_ARGS)
{
int32 arg = PG_GETARG_INT32(0);
PG_RETURN_INT32(arg + 1);
};
This compile sucessfull, but when I try to use:
CREATE OR REPLACE FUNCTION add_one(integer)
RETURNS integer AS
'C:\Documents and Settings\Administrador\Escritorio\test\test.dll',
'pg_finfo_suma'
LANGUAGE 'c' VOLATILE STRICT
COST 1;
I get it:
ERROR: biblioteca «C:\Documents and
Settings\Administrador\Escritorio\test\test.dll» incompatible: no se encuentra
el bloque mágico
HINT: Se requiere que las bibliotecas de extensión usen la macro
PG_MODULE_MAGIC.
Please help me! I don't know to do.
Thanks
Re: [PERFORM] Missing block Magic
Alejandro Carrillo faster...@yahoo.es writes: ERROR: biblioteca «C:\Documents and Settings\Administrador\Escritorio\test\test.dll» incompatible: no se encuentra el bloque mágico HINT: Se requiere que las bibliotecas de extensión usen la macro PG_MODULE_MAGIC. [ scratches head ... ] Your source code looks fine. Are you sure you are pointing to the right copy of the .dll file? If you added the PG_MODULE_MAGIC; line after getting this error, it's likely that the old version of the .dll is already loaded into the server's memory, in which case what you need to do to get rid of it is to restart the server, or at least start a fresh session. 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
Re: [PERFORM] Missing block Magic
I restart and doesn't work. :( Please help me! De: Tom Lane t...@sss.pgh.pa.us Para: Alejandro Carrillo faster...@yahoo.es CC: pgsql-performance@postgresql.org pgsql-performance@postgresql.org Enviado: Martes 5 de junio de 2012 12:00 Asunto: Re: [PERFORM] Missing block Magic Alejandro Carrillo faster...@yahoo.es writes: ERROR: biblioteca «C:\Documents and Settings\Administrador\Escritorio\test\test.dll» incompatible: no se encuentra el bloque mágico HINT: Se requiere que las bibliotecas de extensión usen la macro PG_MODULE_MAGIC. [ scratches head ... ] Your source code looks fine. Are you sure you are pointing to the right copy of the .dll file? If you added the PG_MODULE_MAGIC; line after getting this error, it's likely that the old version of the .dll is already loaded into the server's memory, in which case what you need to do to get rid of it is to restart the server, or at least start a fresh session. 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
