[PERFORM] faster search
Hi- Would someone please enlighten me as to why I'm not seeing a faster execution time on the simple scenario below? there are 412,485 rows in the table and the query matches on 132,528 rows, taking almost a minute to execute. vaccuum analyze was just run. Thanks! Clark test - id| integer partnumber| character varying(32) productlistid | integer typeid| integer Indexes: "test_id" btree (id) "test_plid" btree (productlistid) "test_typeid" btree (typeid) "test_plidtypeid" btree (productlistid, typeid) explain analyze select * from test where productlistid=3 and typeid=9 order by partnumber limit 15; QUERY PLAN - Limit (cost=201073.76..201073.79 rows=15 width=722) (actual time=58092.477..58092.518 rows=15 loops=1) -> Sort (cost=201073.76..201451.76 rows=151200 width=722) (actual time=58092.470..58092.505 rows=15 loops=1) Sort Key: partnumber -> Seq Scan on test (cost=0.00..96458.27 rows=151200 width=722) (actual time=2.515..40201.275 rows=132528 loops=1) Filter: ((productlistid = 3) AND (typeid = 9)) Total runtime: 59664.765 ms (6 rows) System specs: PostgreSQL 7.4.2 on RedHat 9 dual AMD Athlon 2GHz processors 1 gig memory mirrored 7200 RPM IDE disks ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] faster search
hmm, i'm baffled. i simplified the query and it is still taking forever... test - id| integer partnumber| character varying(32) productlistid | integer typeid| integer Indexes: "test_productlistid" btree (productlistid) "test_typeid" btree (typeid) "test_productlistid_typeid" btree (productlistid, typeid) explain analyze select * from test where (productlistid=3 and typeid=9); QUERY PLAN --- Seq Scan on test (cost=0.00..96458.27 rows=156194 width=725) (actual time=516.459..41930.250 rows=132528 loops=1) Filter: ((productlistid = 3) AND (typeid = 9)) Total runtime: 41975.154 ms (3 rows) System specs: PostgreSQL 7.4.2 on RedHat 9 dual AMD Athlon 2GHz processors 1 gig memory mirrored 7200 RPM IDE disks On Fri, 10 Jun 2005, John A Meinel wrote: Clark Slater wrote: Hi- Would someone please enlighten me as to why I'm not seeing a faster execution time on the simple scenario below? there are 412,485 rows in the table and the query matches on 132,528 rows, taking almost a minute to execute. vaccuum analyze was just run. Well, if you are matching 130k out of 400k rows, then a sequential scan is certainly prefered to an index scan. And then you have to sort those 130k rows by partnumber. This *might* be spilling to disk depending on what your workmem/sortmem is set to. I would also say that what you would really want is some way to get the whole thing from an index. And I think the way to do that is: CREATE INDEX test_partnum_listid_typeid_idx ON test(partnumber, productlistid, typeid); VACUUM ANALYZE test; EXPLAIN ANALYZE SELECT * FROM test WHERE productlistid=3 AND typeid=9 ORDER BY partnumber, productlistid, typeid LIMIT 15 ; The trick is that you have to match the order by exactly with the index, so the planner realizes it can do an indexed lookup to get the information. You could also just create an index on partnumber, and see how that affects your original query. I think the planner could use an index lookup on partnumber to get the ordering correct. But it will have to do filtering after the fact based on productlistid and typeid. With my extended index, I think the planner can be smarter and lookup all 3 by the index. Thanks! Clark Good luck, John =:-> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] faster search
thanks for your suggestion. a small improvement. still pretty slow... vbp=# alter table test alter column productlistid set statistics 150; ALTER TABLE vbp=# alter table test alter column typeid set statistics 150; ALTER TABLE vbp=# explain analyze select * from test where (productlistid=3 and typeid=9); QUERY PLAN -- Seq Scan on test (cost=0.00..96458.27 rows=156194 width=725) (actual time=525.617..36802.556 rows=132528 loops=1) Filter: ((productlistid = 3) AND (typeid = 9)) Total runtime: 36847.754 ms (3 rows) Time: 36850.719 ms On Fri, 10 Jun 2005, Joshua D. Drake wrote: Clark Slater wrote: hmm, i'm baffled. i simplified the query and it is still taking forever... What happens if you: alter table test alter column productlistid set statistics 150; alter table test alter column typeid set statistics 150; explain analyze select * from test where (productlistid=3 and typeid=9); Sincerely, Joshua D. Drake test - id| integer partnumber| character varying(32) productlistid | integer typeid| integer Indexes: "test_productlistid" btree (productlistid) "test_typeid" btree (typeid) "test_productlistid_typeid" btree (productlistid, typeid) explain analyze select * from test where (productlistid=3 and typeid=9); QUERY PLAN --- Seq Scan on test (cost=0.00..96458.27 rows=156194 width=725) (actual time=516.459..41930.250 rows=132528 loops=1) Filter: ((productlistid = 3) AND (typeid = 9)) Total runtime: 41975.154 ms (3 rows) System specs: PostgreSQL 7.4.2 on RedHat 9 dual AMD Athlon 2GHz processors 1 gig memory mirrored 7200 RPM IDE disks On Fri, 10 Jun 2005, John A Meinel wrote: Clark Slater wrote: Hi- Would someone please enlighten me as to why I'm not seeing a faster execution time on the simple scenario below? there are 412,485 rows in the table and the query matches on 132,528 rows, taking almost a minute to execute. vaccuum analyze was just run. Well, if you are matching 130k out of 400k rows, then a sequential scan is certainly prefered to an index scan. And then you have to sort those 130k rows by partnumber. This *might* be spilling to disk depending on what your workmem/sortmem is set to. I would also say that what you would really want is some way to get the whole thing from an index. And I think the way to do that is: CREATE INDEX test_partnum_listid_typeid_idx ON test(partnumber, productlistid, typeid); VACUUM ANALYZE test; EXPLAIN ANALYZE SELECT * FROM test WHERE productlistid=3 AND typeid=9 ORDER BY partnumber, productlistid, typeid LIMIT 15 ; The trick is that you have to match the order by exactly with the index, so the planner realizes it can do an indexed lookup to get the information. You could also just create an index on partnumber, and see how that affects your original query. I think the planner could use an index lookup on partnumber to get the ordering correct. But it will have to do filtering after the fact based on productlistid and typeid. With my extended index, I think the planner can be smarter and lookup all 3 by the index. Thanks! Clark Good luck, John =:-> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] faster search
Query should return 132,528 rows. vbp=# set enable_seqscan = false; SET vbp=# explain analyze select * from test where (productlistid=3 and typeid=9); QUERY PLAN Index Scan using test_typeid on test (cost=0.00..137223.89 rows=156194 width=725) (actual time=25.999..25708.478 rows=132528 loops=1) Index Cond: (typeid = 9) Filter: (productlistid = 3) Total runtime: 25757.679 ms (4 rows) On Fri, 10 Jun 2005, Joshua D. Drake wrote: Clark Slater wrote: thanks for your suggestion. a small improvement. still pretty slow... vbp=# alter table test alter column productlistid set statistics 150; ALTER TABLE vbp=# alter table test alter column typeid set statistics 150; ALTER TABLE vbp=# explain analyze select * from test where (productlistid=3 and Hello, Also what happens if you: set enable_seqscan = false; explain analyze query Sincerely, Joshua D. Drake typeid=9); QUERY PLAN -- Seq Scan on test (cost=0.00..96458.27 rows=156194 width=725) (actual time=525.617..36802.556 rows=132528 loops=1) Filter: ((productlistid = 3) AND (typeid = 9)) Total runtime: 36847.754 ms (3 rows) Time: 36850.719 ms On Fri, 10 Jun 2005, Joshua D. Drake wrote: Clark Slater wrote: hmm, i'm baffled. i simplified the query and it is still taking forever... What happens if you: alter table test alter column productlistid set statistics 150; alter table test alter column typeid set statistics 150; explain analyze select * from test where (productlistid=3 and typeid=9); Sincerely, Joshua D. Drake test - id| integer partnumber| character varying(32) productlistid | integer typeid| integer Indexes: "test_productlistid" btree (productlistid) "test_typeid" btree (typeid) "test_productlistid_typeid" btree (productlistid, typeid) explain analyze select * from test where (productlistid=3 and typeid=9); QUERY PLAN --- Seq Scan on test (cost=0.00..96458.27 rows=156194 width=725) (actual time=516.459..41930.250 rows=132528 loops=1) Filter: ((productlistid = 3) AND (typeid = 9)) Total runtime: 41975.154 ms (3 rows) System specs: PostgreSQL 7.4.2 on RedHat 9 dual AMD Athlon 2GHz processors 1 gig memory mirrored 7200 RPM IDE disks On Fri, 10 Jun 2005, John A Meinel wrote: Clark Slater wrote: Hi- Would someone please enlighten me as to why I'm not seeing a faster execution time on the simple scenario below? there are 412,485 rows in the table and the query matches on 132,528 rows, taking almost a minute to execute. vaccuum analyze was just run. Well, if you are matching 130k out of 400k rows, then a sequential scan is certainly prefered to an index scan. And then you have to sort those 130k rows by partnumber. This *might* be spilling to disk depending on what your workmem/sortmem is set to. I would also say that what you would really want is some way to get the whole thing from an index. And I think the way to do that is: CREATE INDEX test_partnum_listid_typeid_idx ON test(partnumber, productlistid, typeid); VACUUM ANALYZE test; EXPLAIN ANALYZE SELECT * FROM test WHERE productlistid=3 AND typeid=9 ORDER BY partnumber, productlistid, typeid LIMIT 15 ; The trick is that you have to match the order by exactly with the index, so the planner realizes it can do an indexed lookup to get the information. You could also just create an index on partnumber, and see how that affects your original query. I think the planner could use an index lookup on partnumber to get the ordering correct. But it will have to do filtering after the fact based on productlistid and typeid. With my extended index, I think the planner can be smarter and lookup all 3 by the index. Thanks! Clark Good luck, John =:-> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org ---
