[PERFORM] UNION ALL vs INHERITANCE

2004-12-16 Thread Adi Alurkar
Greetings, Why does the append resulting from a inheritance take longer than one resulting from UNION ALL? summary: Append resulting from inheritance: -> Append (cost=0.00..17.43 rows=2 width=72) (actual time=3.876..245.320 rows=28 loops=1) Append resulting from UNION ALL: -> Append (cost

Re: [PERFORM] Seqscan rather than Index

2004-12-16 Thread David Brown
> You might want to reduce random_page_cost a little. > Keep in mind that your test case is small enough to fit in RAM and is > probably not reflective of what will happen with larger tables. I am also running 8.0 rc1 for Windows. Despite many hours spent tweaking various planner cost constants,

Re: [PERFORM] UNION ALL vs INHERITANCE

2004-12-16 Thread Tom Lane
Adi Alurkar <[EMAIL PROTECTED]> writes: > Why does the append resulting from a inheritance take longer than one > resulting from UNION ALL? The index scan is where the time difference is: > -> Index Scan using fftiallbgrgfid_1102715649 on > f_f_all_base (cost=0.00..3.

Re: [PERFORM] Seqscan rather than Index

2004-12-16 Thread Tom Lane
Jon Anderson <[EMAIL PROTECTED]> writes: > Any hints on what to do to make PostgreSQL use the index? You might want to reduce random_page_cost a little. Keep in mind that your test case is small enough to fit in RAM and is probably not reflective of what will happen with larger tables.

Re: [PERFORM] Improve performance of query

2004-12-16 Thread Tom Lane
Richard Rowell <[EMAIL PROTECTED]> writes: > I'm trying to port our application from MS-SQL to Postgres. We have > implemented all of our rather complicated application security in the > database. The query that follows takes a half of a second or less on > MS-SQL server and around 5 seconds on P

Re: [PERFORM] Improve performance of query

2004-12-16 Thread John A Meinel
The first thing to check... Did you do a recent VACUUM ANALYZE? This updates all the statistics. There are a number of places where it says "rows=1000" which is usually the "I have no idea, let me guess 1000". Also, there are a number of places where the estimates are pretty far off. For instance:

Re: [PERFORM] Improve performance of query

2004-12-16 Thread Richard Huxton
Richard Rowell wrote: I'm trying to port our application from MS-SQL to Postgres. We have implemented all of our rather complicated application security in the database. The query that follows takes a half of a second or less on MS-SQL server and around 5 seconds on Postgres. My concern is that

Re: [PERFORM] Improve performance of query

2004-12-16 Thread Stephen Frost
* Richard Rowell ([EMAIL PROTECTED]) wrote: > I have included an EXPLAIN ANALYZE, relevant table counts, and relevant > indexing information. If anyone has any suggestions on how to improve > performance TIA! Just a thought- do the UNION's actually have to be union's or would having them be

[PERFORM] Seqscan rather than Index

2004-12-16 Thread Jon Anderson
I have a table 'Alias' with 541162 rows. It's created as follows: CREATE TABLE alias ( id int4 NOT NULL, person_id int4 NOT NULL, last_name varchar(30), first_name varchar(30), middle_name varchar(30), questioned_identity_flag varchar, CONSTRAINT alias_pkey PRIMARY KEY (id) ) Afte

[PERFORM] Improve performance of query

2004-12-16 Thread Richard Rowell
I'm trying to port our application from MS-SQL to Postgres. We have implemented all of our rather complicated application security in the database. The query that follows takes a half of a second or less on MS-SQL server and around 5 seconds on Postgres. My concern is that this data set is rathe