Re: [GENERAL] Slow query with sub-select

2011-07-16 Thread Rick Genter
On Jul 16, 2011, at 4:14 PM, - - wrote: > I would like to count rows in q whose mid does not exist in t. I would write such a query like this: SELECT COUNT(*) FROM q LEFT OUTER JOIN t ON (t.mid = q.mid) WHERE t.mid IS NULL; And I would make sure there was an index on t.mid. (

Re: [GENERAL] Slow query with sub-select

2011-07-16 Thread - -
> - - writes: > > The weird thing is that before I updated my server the query was about 5 > > times faster. > > I've googled and I think the problem lies with the under-estimation of the > > query planner about the number of rows in the nested table.I will be trying > > the 'set enable_seqsca

[GENERAL] pass XML as an argument and INSERT (key/value pairs) in postgresql

2011-07-16 Thread cesitarps
hola amigos encontre este procedimiento pero quisiera pasarlo a postgresql , su ayuda sera de mucha importancia porfavor se los agradecere. ¿ como seria en postgresql ? create procedure ParseXML (@InputXML xml) as begin declare @MyTable table ( id int, value int )

Re: [GENERAL] Slow query with sub-select

2011-07-16 Thread Michael Nolan
2011/7/16 - - > > The weird thing is that before I updated my server the query was about 5 > times faster. > Updated it from what to what, and how? -- Mike Nolan no...@tssi.com

Re: [GENERAL] Table dublicates values

2011-07-16 Thread Adrian Klaver
On Saturday, July 16, 2011 1:47:50 am Deniz Atak wrote: > Hi all, > > I am using postgresql on Glassfish server. When I restart the server for > some reason, one of my databases' tables duplicates the values. For example > aTable is from aDatabase: > > select * from aTable > col1

Re: [GENERAL] Slow query with sub-select

2011-07-16 Thread Tom Lane
- - writes: > The weird thing is that before I updated my server the query was about 5 > times faster. > I've googled and I think the problem lies with the under-estimation of the > query planner about the number of rows in the nested table.I will be trying > the 'set enable_seqscan = false' so

Re: [GENERAL] Slow query with sub-select

2011-07-16 Thread - -
On Jul 16, 2011, at 6:32, - - wrote: The following query seems to take ages despite the EXPLAIN stating that an index is used.Also, the condition (WHERE t.mid = q.mid) should be a one-to-one mapping, should it not? In this case the mapping is to 3641527 rows. Table q has no indexes and not

Re: [GENERAL] Table dublicates values

2011-07-16 Thread Rob Sargent
Deniz Atak wrote: Hi all, I am using postgresql on Glassfish server. When I restart the server for some reason, one of my databases' tables duplicates the values. For example aTable is from aDatabase: select * from aTable col1 | col2 ---+- text/html

Re: [GENERAL] Slow query with sub-select

2011-07-16 Thread David Johnston
On Jul 16, 2011, at 6:32, - - wrote: > The following query seems to take ages despite the EXPLAIN stating that an > index is used. > Also, the condition (WHERE t.mid = q.mid) should be a one-to-one mapping, > should it not? In this case the mapping is to 3641527 rows. > > Table q has no inde

[GENERAL] Table dublicates values

2011-07-16 Thread Deniz Atak
Hi all, I am using postgresql on Glassfish server. When I restart the server for some reason, one of my databases' tables duplicates the values. For example aTable is from aDatabase: select * from aTable col1| col2 ---+- text/html |1672 t

[GENERAL] Slow query with sub-select

2011-07-16 Thread - -
The following query seems to take ages despite the EXPLAIN stating that an index is used.Also, the condition (WHERE t.mid = q.mid) should be a one-to-one mapping, should it not? In this case the mapping is to 3641527 rows. Table q has no indexes and not referenced by other tables. Table t has a