Skip to content

Instantly share code, notes, and snippets.

@jon-zawada

jon-zawada/Postgres Core Query

Created April 9, 2020 04:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jon-zawada/4fbea4c94f93032c8550167e18cf4fa6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jon-zawada/4fbea4c94f93032c8550167e18cf4fa6 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jon-zawada revised this gist Apr 9, 2020. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions Postgres Core Query
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    nimbus=# SELECT * FROM songs JOIN users ON songs.id_user = users.id LIMIT 3;
    id | song_name | song_plays | song_comments | song_image_url | song_genre | id_playlist | id_user | id | user_name | user_imageurl | user_location | user_follower_count
    ----+--------------------------+------------+---------------+--------------------------------------+------------+-------------+---------+---------+-------------------+---------------------------------------------------------------------+--------------------+---------------------
    1 | ab voluptatem et | 88405 | 84363 | http://lorempixel.com/640/480/cats | edm | 125840 | 997611 | 997611 | Mrs. Vicente Hand | https://s3.amazonaws.com/uifaces/faces/twitter/brandonburke/128.jpg | Majorburgh | 55671
  2. jon-zawada created this gist Apr 9, 2020.
    18 changes: 18 additions & 0 deletions Postgres Core Query
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    id | song_name | song_plays | song_comments | song_image_url | song_genre | id_playlist | id_user | id | user_name | user_imageurl | user_location | user_follower_count
    ----+--------------------------+------------+---------------+--------------------------------------+------------+-------------+---------+---------+-------------------+---------------------------------------------------------------------+--------------------+---------------------
    1 | ab voluptatem et | 88405 | 84363 | http://lorempixel.com/640/480/cats | edm | 125840 | 997611 | 997611 | Mrs. Vicente Hand | https://s3.amazonaws.com/uifaces/faces/twitter/brandonburke/128.jpg | Majorburgh | 55671
    2 | velit accusantium labore | 85421 | 83485 | http://lorempixel.com/640/480/cats | country | 248725 | 7734294 | 7734294 | Adrianna Dietrich | https://s3.amazonaws.com/uifaces/faces/twitter/sdidonato/128.jpg | North Gisselletown | 92846
    3 | qui neque veniam | 20481 | 73833 | http://lorempixel.com/640/480/nature | edm | 460079 | 9040482 | 9040482 | April Haley | https://s3.amazonaws.com/uifaces/faces/twitter/denisepires/128.jpg | Walkerton | 27442
    (3 rows)

    nimbus=# EXPLAIN ANALYZE SELECT * FROM songs JOIN users ON songs.id_user = users.id LIMIT 3;
    QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------------
    Limit (cost=0.43..2.10 rows=3 width=184) (actual time=0.061..0.077 rows=3 loops=1)
    -> Nested Loop (cost=0.43..5539146.51 rows=10000064 width=184) (actual time=0.060..0.075 rows=3 loops=1)
    -> Seq Scan on songs (cost=0.00..244653.64 rows=10000064 width=83) (actual time=0.037..0.037 rows=3 loops=1)
    -> Index Scan using users_pkey on users (cost=0.43..0.53 rows=1 width=101) (actual time=0.009..0.009 rows=1 loops=3)
    Index Cond: (id = songs.id_user)
    Planning Time: 2.505 ms
    Execution Time: 1.405 ms
    (7 rows)