#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0

    View has different query plan than select statement


    Hi,

    I have created a table 'test_table' and index 'idx_test_table' with a view 'v_test_table'. However the query plan used by the view does not use the index but when running the select statement itself it does use the index. Given that hints are not available in Postgres 9.1 how can I persuade the view to use the same query plan as the select statement?

    Thanks,

    Tim


    --DROP table test_table CASCADE;

    -- create test table
    CREATE TABLE test_table (
    history_id SERIAL,
    id character varying(50) NOT NULL ,
    name character varying(50),
    CONSTRAINT test_table_pkey PRIMARY KEY (history_id)
    );

    -- create index on test table
    CREATE INDEX idx_test_table ON test_table (id);

    -- populate test table
    INSERT INTO test_table (id, name) SELECT *, 'Danger Mouse' FROM (SELECT md5(random()::text) from generate_series(1,10000)) q;

    -- collect stats
    ANALYZE test_table;


    EXPLAIN (ANALYZE, BUFFERS)
    SELECT *
    FROM test_table
    WHERE id = '02b304b1c54542570d9f7bd39361f5b4';

    "Index Scan using idx_test_table on test_table (cost=0.00..8.27 rows=1 width=50) (actual time=0.021..0.022 rows=1 loops=1)"
    " Index Cond: ((id)::text = '02b304b1c54542570d9f7bd39361f5b4'::text)"
    " Buffers: shared hit=3"
    "Total runtime: 0.051 ms"


    -- select statement with good plan

    EXPLAIN (ANALYZE, BUFFERS)
    SELECT id,
    CASE WHEN COALESCE(LAG(name) OVER (PARTITION BY id ORDER BY history_id), name || 'x') <> name
    then name
    end as name
    FROM test_table
    WHERE id = '02b304b1c54542570d9f7bd39361f5b4';

    "WindowAgg (cost=8.28..8.31 rows=1 width=50) (actual time=0.050..0.051 rows=1 loops=1)"
    " Buffers: shared hit=3"
    " -> Sort (cost=8.28..8.29 rows=1 width=50) (actual time=0.039..0.039 rows=1 loops=1)"
    " Sort Key: history_id"
    " Sort Method: quicksort Memory: 25kB"
    " Buffers: shared hit=3"
    " -> Index Scan using idx_test_table on test_table (cost=0.00..8.27 rows=1 width=50) (actual time=0.030..0.031 rows=1 loops=1)"
    " Index Cond: ((id)::text = '02b304b1c54542570d9f7bd39361f5b4'::text)"
    " Buffers: shared hit=3"
    "Total runtime: 0.102 ms"


    --DROP VIEW v_test_table;

    CREATE OR REPLACE VIEW v_test_table AS
    SELECT id,
    CASE WHEN COALESCE(LAG(name) OVER (PARTITION BY id ORDER BY history_id), name || 'x') <> name
    then name
    end as name
    FROM test_table;


    -- Query via view with bad plan

    EXPLAIN (ANALYZE, BUFFERS)
    SELECT *
    FROM v_test_table
    WHERE id = '02b304b1c54542570d9f7bd39361f5b4';

    "Subquery Scan on v_test_table (cost=868.39..1243.39 rows=50 width=65) (actual time=26.115..33.327 rows=1 loops=1)"
    " Filter: ((v_test_table.id)::text = '02b304b1c54542570d9f7bd39361f5b4'::text)"
    " Buffers: shared hit=104, temp read=77 written=77"
    " -> WindowAgg (cost=868.39..1118.39 rows=10000 width=50) (actual time=26.022..32.519 rows=10000 loops=1)"
    " Buffers: shared hit=104, temp read=77 written=77"
    " -> Sort (cost=868.39..893.39 rows=10000 width=50) (actual time=26.013..27.796 rows=10000 loops=1)"
    " Sort Key: test_table.id, test_table.history_id"
    " Sort Method: external merge Disk: 608kB"
    " Buffers: shared hit=104, temp read=77 written=77"
    " -> Seq Scan on test_table (cost=0.00..204.00 rows=10000 width=50) (actual time=0.010..1.804 rows=10000 loops=1)"
    " Buffers: shared hit=104"
    "Total runtime: 33.491 ms"




    How can I get the view to use the same query plan as the select statement?
    Last edited by mit_jones; August 29th, 2013 at 08:13 PM. Reason: spelling correction in title
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0
    Assuming that there is no way to make this view performant in Postgres 9.1 (I hope that this is not the case) and given the current 'official' stance on hints I wonder if large Postgres databases are really viable. Or does this issue occur so rarely I have just been extremely unlucky (I doubt it).

    What does one do when faced with a suboptimal query plan? Sip your coffee more slowly while you wait for your results? Raise the heckles of those with far more ability than me to change their minds and implement hints by writing posts such as this? Perhaps the fact that in all these years there has yet been no viable alternative solution implemented 'that avoids the problems that have been observed with other hint systems' that there may be no other solution other than to bite the bullet and implement hints.

    One can argue the toss as to the pros and cons but the fact is that if this view cannot be made performant in Postgres 9.1 then there are other databases that most probably can with the simple (for the coder) addition of a hint e.g. Oracle's PUSH_PRED hint.


    In 2011 (blog/2011/Feb/Why-the-F%2525-Doesnt-Postgres-Have-Hints!!.html) a prophecy was made "... I think it will probably be at least another 5 years before it happens though; there isnít enough overlap with the Oracle and Postgres communities to realize how many people are not able to migrate to Postgres because of planner/optimizer deficiencies; but I do think that day will come. "


    Yours, sincerely trawling,

    Tim
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    1
    Rep Power
    0

    Thumbs up


    I think you hit the nail on the head here Tim.

    I have seen several posts about the pros and cons of Hints, but all posts on the topic I have seen say basically:
    post an example of something that does not work.

    You have done that, and seem to have followed the guidelines on the postgres.org wiki on the Slow_Query_Questions page and "Guide_to_reporting_problems" page, so any postgres developer should be able to reproduce it.

    My only suggestion is that perhaps you need to post this to the pgsql-performance mailing list (as described on the Slow_Query_Questions page - sorry, cannot post a URL as I have newly created profile.). That might get the official developers to see the problem quicker.

    I eagerly await the outcome to this, as I also think this is an issue.

    Andy

IMN logo majestic logo threadwatch logo seochat tools logo