[PERFORM] Improving performance on multicolumn query

2005-11-09 Thread Jan Kesten
Hi, all! I've been using postgresql for a long time now, but today I had some problem I couldn't solve properly - hope here some more experienced users have some hint's for me. First, I'm using postgresql 7.4.7 on a 2GHz machine having 1.5GByte RAM and I have a table with about 220 columns and

Re: [PERFORM] Improving performance on multicolumn query

2005-11-09 Thread Richard Huxton
Jan Kesten wrote: First, I'm using postgresql 7.4.7 on a 2GHz machine having 1.5GByte RAM and I have a table with about 220 columns and 2 rows - and the first five columns build a primary key (and a unique index). transfer= explain analyse SELECT * FROM test WHERE test_a=9091150001 AND

Re: [PERFORM] Improving performance on multicolumn query

2005-11-09 Thread Steinar H. Gunderson
On Wed, Nov 09, 2005 at 01:08:07PM +0100, Jan Kesten wrote: First, I'm using postgresql 7.4.7 on a 2GHz machine having 1.5GByte RAM and I have a table with about 220 columns and 2 rows - and the first five columns build a primary key (and a unique index). I forgot this, but it should be

Re: [PERFORM] Improving performance on multicolumn query

2005-11-09 Thread Christopher Kings-Lynne
transfer= explain analyse SELECT * FROM test WHERE test_a=9091150001 AND test_b=1 AND test_c=2 AND test_d=0 AND test_e=0; Index Scan using test_idx on test (cost=0.00..50.27 rows=1 width=1891) (actual time=0.161..0.167 rows=1 loops=1) Index Cond: (test_a = 9091150001::bigint) Filter:

Re: [PERFORM] Improving performance on multicolumn query

2005-11-09 Thread Steinar H. Gunderson
On Wed, Nov 09, 2005 at 01:08:07PM +0100, Jan Kesten wrote: Now my problem: I need really many queries of rows using it's primary key and fetching about five different columns but these are quite slow (about 10 queries per second and as I have some other databases which can have about 300

Re: [PERFORM] Improving performance on multicolumn query

2005-11-09 Thread Jan Kesten
Hi all! First thanks to any answer by now :-) You don't post your table definitions (please do), but it looks like test_b, test_c, test_d and test_e might be bigints? If so, you may want to do explicit AND test_b=1::bigint AND test_c=2::bigint etc. -- 7.4 doesn't figure this out for you.

Re: [PERFORM] Improving performance of a query

2005-09-06 Thread Merlin Moncure
Carlos wrote: SELECT * FROM SSIRRA where (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART = 00) or (YEAR = 2004 and CUSTOMER = 04 and CODE 00) or (YEAR = 2004 and CUSTOMER 04) or (YEAR 2004) [snip] ah, the positional query. You can always rewrite this query in

Re: [PERFORM] Improving performance of a query

2005-09-06 Thread Stephan Szabo
On Tue, 6 Sep 2005, Merlin Moncure wrote: Carlos wrote: SELECT * FROM SSIRRA where (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART = 00) or (YEAR = 2004 and CUSTOMER = 04 and CODE 00) or (YEAR = 2004 and CUSTOMER 04) or (YEAR 2004) [snip] ah, the

Re: [PERFORM] Improving performance of a query

2005-09-06 Thread Merlin Moncure
Carlos wrote: SELECT * FROM SSIRRA where (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART = 00) or (YEAR = 2004 and CUSTOMER = 04 and CODE 00) or (YEAR = 2004 and CUSTOMER 04) or (YEAR 2004) [snip] ah, the positional query. You can always rewrite

[PERFORM] Improving performance of a query

2005-09-03 Thread Carlos Benkendorf
Hi, Is there a way to improve the performance of the following query? SELECT * FROM SSIRRA where (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART = 00) or (YEAR = 2004 and CUSTOMER = 04 and CODE 00) or (YEAR = 2004 and CUSTOMER 04) or (YEAR 2004) Thanks in