Steve Atkins wrote:
On Fri, Jun 10, 2005 at 01:45:05PM -0400, 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?
[...]
Create an index on (productlistid, typeid, partnumber) then
select * from test
John A Meinel [EMAIL PROTECTED] writes:
I am a little surprised that it is taking 40s to scan only 400k rows,
though.
Yeah, that seemed high to me too. Table bloat maybe? It would be
interesting to look at the output of vacuum verbose test to see
how much dead space there is.
On Fri, Jun 10, 2005 at 01:45:05PM -0400, Clark Slater wrote:
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
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
[Clark Slater - Fri at 01:45:05PM -0400]
Would someone please enlighten me as
to why I'm not seeing a faster execution
time on the simple scenario below?
Just some thoughts from a novice PG-DBA .. :-)
My general experience is that PG usually prefers sequal scans to indices if
a large portion
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,
On Fri, Jun 10, 2005 at 01:45:05PM -0400, 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?
Because you need to extract a huge number of rows via a seqscan, sort
them and then throw them away, I think.
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
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
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
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
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
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
Clark Slater wrote:
Query should return 132,528 rows.
O.k. then the planner is doing fine it looks like. The problem is you
are pulling 132,528 rows. I would suggest moving to a cursor which will
allow you to fetch in smaller chunks much quicker.
Sincerely,
Joshua D. Drake
vbp=# set
14 matches
Mail list logo
