[PERFORM] Seq scan on zero-parameters function

2004-02-06 Thread Octavio Alvarez
Hi! I'd like to know if this is expected behavior. These are two couples of queries. In each couple, the first one has a WHERE field = function() condition, just like the second one, but in the form WHERE field = (SELECT function()). In my opinion, both should have the same execution plan, as

Re: [PERFORM] Seq scan on zero-parameters function

2004-02-06 Thread Octavio Alvarez
Tomasz Myrta said: Dnia 2004-02-06 08:19, U¿ytkownik Octavio Alvarez napisa³: In each couple, the first one has a WHERE field = function() condition, just like the second one, but in the form WHERE field = (SELECT function()). In my opinion, both should have the same execution plan, as the

Re: [PERFORM] Increase performance of a UNION query that thakes 655.07 msec to be runned ?

2004-02-06 Thread Tom Lane
Bruno BAGUETTE [EMAIL PROTECTED] writes: Do you see a way to get better performances with this query which takes currently 655.07 msec to be done. levure= explain analyze SELECT distinct lower(substr(l_name, 1, 1)) AS initiale FROM people levure- UNION levure- SELECT distinct

Re: [PERFORM] Increase performance of a UNION query that thakes

2004-02-06 Thread Stephan Szabo
On Fri, 6 Feb 2004, Bruno BAGUETTE wrote: I was thinking that a index on lower(substr(l_name, 1, 1)) and another index on lower(substr(org_name, 1, 1)) should gives better performances. When I've to create theses two indexes, it seems like this is not allowed : levure= CREATE INDEX

RE : [PERFORM] Increase performance of a UNION query that thakes 655.07 msec to be runned ?

2004-02-06 Thread Bruno BAGUETTE
re-Hello, As suggested by Tom, I've removed the distinct and tried it's query : levure= explain analyze select initiale from ( levure( select lower(substr(l_name,1,1)) as initiale from people levure( union all levure( select lower(substr(org_name,1,1)) as initiale from organizations

RE : [PERFORM] Increase performance of a UNION query that thakes 655.07 msec to be runned ?

2004-02-06 Thread Bruno BAGUETTE
In addition to what Tom said, the row estimates look suspiciously default. You mention vacuuming, but do you ever analyze the tables? I run VACUUM FULL ANALYZE with the postgres user on all the PostgreSQL databases on the server, twice a day, sometimes more. Also, what do you have sort_mem

Re: RE : [PERFORM] Increase performance of a UNION query that thakes

2004-02-06 Thread Stephan Szabo
On Fri, 6 Feb 2004, Bruno BAGUETTE wrote: In addition to what Tom said, the row estimates look suspiciously default. You mention vacuuming, but do you ever analyze the tables? I run VACUUM FULL ANALYZE with the postgres user on all the PostgreSQL databases on the server, twice a day,

Re: [PERFORM] COPY with INDEXES question

2004-02-06 Thread Rod Taylor
On Thu, 2004-02-05 at 19:46, Slavisa Garic wrote: Hi, I have a quick question. In order to speed up insertion of large number of rows (100s of thousands) I replaced the INSERT with the COPY. This works fine but one question popped into my mind. Does copy updates indexes on that table if

Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?

2004-02-06 Thread Jan Wieck
Mike Nolan wrote: Seriously, I am tired of this kind of question. You gotta get bold enough to stand up in a meeting like that, say guy's, you can ask me how this compares to Oracle ... but if you're seriously asking me how this compares to MySQL, call me again when you've done your homework.

Re: [PERFORM] Database conversion woes...

2004-02-06 Thread Kevin Carpenter
First just wanted to say thank you all for the quick and helpful answers. With all the input I know I am on the right track. With that in mind I created a perl script to do my migrations and to do it based on moving from a db name to a schema name. I had done alot of the reading on

Re: [PERFORM] Why is query selecting sequential?

2004-02-06 Thread Josh Berkus
Karl, SubPlan - Seq Scan on forumlog (cost=0.00..1.18 rows=1 width=8) Filter: ((login = '%s'::text) AND (forum = '%s'::text) AND (number = $0)) Why is the subplan using a sequential scan? At minimum the index on the post number (forumlog_number)

Re: [PERFORM] Why is query selecting sequential?

2004-02-06 Thread Josh Berkus
Karl, Well, still with only 5 rows in the forumlog table you're not going get realistic results compared to a loaded database. However, you are making things difficult for the parser with awkward query syntax; what you currently have encourages a sequential loop. If there are potentially

Re: [PERFORM] 7.3 vs 7.4 performance

2004-02-06 Thread Orion Henry
On Fri, 2004-02-06 at 02:43, Hannu Krosing wrote: Orion Henry kirjutas N, 05.02.2004 kell 07:16: I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30% slower than 7.3.4. Is this common knowledge or am I just unlucky with my query/data selection? Things of note that

Re: [PERFORM] 7.3 vs 7.4 performance

2004-02-06 Thread Orion Henry
On Wed, 2004-02-04 at 21:27, Josh Berkus wrote: Orion, I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30% slower than 7.3.4. Is this common knowledge or am I just unlucky with my query/data selection? No, it's not common knowledge. It should be the other way around.

Re: [PERFORM] 7.3 vs 7.4 performance

2004-02-06 Thread Josh Berkus
Orion, Here's one good example of 7.3 beating 7.4 soundly: Again this could me some compile option since I built the 7.4 RPM from source and I got the 7.3 from Fedora or something to do with the Opteron architecture. (Yes the compiled postgres is 64 bit) Need an EXPLAIN ANALYZE, not just