[Please don't top-post; it makes the discussion hard to follow.]
tuanhoanganh wrote:
> Greg Williamson wrote:
>> Did you run an analyze on the table after building the new
>> indexes? The row estimates seem to be off wildly, although that
>> may be a symptom of something else
I think that's because the optimizer doesn't know how to estimate the
range test properly, and resorts to "magic numbers" based on
percentages of the rows in the table.
> If i remove ORDER BY, the query run faster.
Yeah, it thinks there will be 26 billion rows, and that sorting that
would be very expensive. You really have only 76 thousand rows,
which wouldn't be so bad. I'm not sure whether this would work, but
if you need the ordering, you might try:
WITH x AS SELECT * FROM x ORDER BY d.data_id;
-Kevin