From: | "Bryan White" <bryan(at)arcamax(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PG 7.0 is 2.5 times slower running a big report |
Date: | 2000-05-25 02:45:36 |
Message-ID: | 002d01bfc5f3$4e8c3700$0200a8c0@nwptn1.va.home.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> You can force sort-based or index-based ordering by issuing
> SET enable_indexscan = OFF;
> or
> SET enable_sort = OFF;
> respectively. I suggest that the next step should be to see what
> EXPLAIN says for all four queries in both cases (so we can see what
> the planner's estimates for the two cases actually are for each
> table), and then to measure the actual runtimes of each of the
> SELECTs both ways.
Here is to comparison:
explain select ... from customer order by custid;
Sort (cost=598354.56..598354.56 rows=2446621 width=40)
-> Seq Scan on customer (cost=0.00..75939.21 rows=2446621 width=40)
Index Scan using icusid on customer (cost=0.00..823287.37 rows=2446621
width=40)
Choice was sort
explain select ... from orders order by custid;
Sort (cost=167945.80..167945.80 rows=588242 width=60)
-> Seq Scan on orders (cost=0.00..31399.42 rows=588242 width=60)
Index Scan using iordcus3 on orders (cost=0.00..326167.12 rows=588242
width=60)
Choice was sort
explain select ... from contact order by custid;
Sort (cost=1874247.64..1874247.64 rows=6462635 width=44)
-> Seq Scan on contact (cost=0.00..141404.35 rows=6462635 width=44)
Index Scan using iconcus4 on contact (cost=0.00..1446338.62 rows=6462635
width=44)
Choice was index
explain select ... from custlist order by custid;
Sort (cost=469342.83..469342.83 rows=2738543 width=8)
-> Seq Scan on custlist (cost=0.00..42109.43 rows=2738543 width=8)
Index Scan using iclcust3 on custlist (cost=0.00..334501.73 rows=2738543
width=8)
Choice was index
From | Date | Subject | |
---|---|---|---|
Next Message | Bryan White | 2000-05-25 02:57:14 | Re: PG 7.0 is 2.5 times slower running a big report |
Previous Message | Tom Lane | 2000-05-25 02:29:06 | Re: PG 7.0 is 2.5 times slower running a big report |